sybase proxy database univarchar为空问题

问题描述：

使用sybase proxy database，在local server上使用insert into ... select ... 向proxy database中copy数据时，univarchar字段为null，其他字段正常。

测试环境及步骤：

	Descriptions
Server	LOCALSVR, REMOTESVR
OS	SunOS 5.10 Generic_144489-14 i86pc i386 i86pc
ASE Version	ASE15.5 EBF 19901 SMP ESD#5.1
Default Character Set	ISO 8859-1 (Latin-1)
Database	REMOTESVR..testdb, LOCALSVR..proxy_testdb, LOCALSVR..local_testdb
User	sa

 1.       Create remote database onREMOTESVR

Create Database testdb on data1 = 10 log onlog1 = 10

go

EXEC sp_dboption testdb ,"abort tranon log full",true

go

EXEC sp_dboption testdb ,"selectinto/bulkcopy/pllsort",true

go

EXEC sp_dboption testdb ,"trunc log onchkpt",true

go

use testdb

go

CHECKPOINT

Go

 

2.       Create table in remotedatabase on REMOTESVR

CREATE TABLE  tb1                                               

 (                                                                     

 binary_col               binary(8)                    NOT NULL,       

 univarchar_col           univarchar(10)                  NULL

 )

 

3.       Create proxy database onLOCALSVR

create database proxy_testdb on data1=10log on log1=10

with default_location="REMOTESVR.testdb.dbo."

for proxy_update

go

EXEC sp_dboption proxy_testdb,"selectinto/bulkcopy/pllsort",true

go

use proxy_testdb

go

CHECKPOINT

Go

 

4.       Create local database on LOCALSVR

Create Database local_testdb on data1 = 10log on log1 = 10

go

EXEC sp_dboption local_testdb ,"aborttran on log full",true

go

EXEC sp_dboption local_testdb ,"selectinto/bulkcopy/pllsort",true

go

EXEC sp_dboption local_testdb ,"trunclog on chkpt",true

go

use local_testdb

go

CHECKPOINT

Go

 

5.       Create local table tb1 inlocal_testdb on LOCALSVR

CREATE TABLE tb1                                                

 (                                                                     

 binary_col               binary(8)                    NOTNULL,       

 univarchar_col           univarchar(10)                  NULL

 )

 

6.       Insert data toLOCALSVR..local_testdb..tb1

Insert into tb1 (binary_col,univarchar_col)values (0x00001900004f7026,'23735')

Go

Select * from local_testdb..tb1

go

binary_col                           univarchar_col

 ------------------                 ------------------------------------------

 0x00001900004f7026      0x32003300370033003500

 

7.       Import data toproxy_testdb..tb1 from local_testdb..tb1 on LOCALSVR

insert proxy_testdb..tb1(binary_col,univarchar_col) select binary_col,univarchar_col fromlocal_testdb..tb1

go

 

8.       Result

select * from proxy_testdb..tb1

go

解决方案：

最后找到原因了，是因为两个server的"enable unicode normalization"参数不一致导致的....

local server启用了normalization, remote server禁用了，所以转换时univarchar变成了null. 这个参数一旦禁用就不能启用了。。。 最后通过程序避开了这个问题，没禁用local server端的这个参数。

参数说明： http://infocenter.sybase.com/help/index.jsp

"
Activates Unilib character normalization. The normalization process modifies the data so there is only a single representation in the database for a given sequence of abstract characters. Often, characters followed by combined diacritics are replaced by precombined forms.

Set enable unicode normalization to 1 to use the built-in process that enforces normalization on all incoming Unicode data. If this parameter is disabled (set to 0), the normalization step is bypassed and the client code is responsible for normalization rather than the server. If normalization is disabled, performance is improved—but only if all clients present Unicode data to the server using the same representation.

Note： Once disabled, normalization cannot be turned on again. This one-way change prevents non-normalized data from entering the data base.
"