mardi 30 décembre 2014

SQL Server 2008 R2 native replication with Column Level Collation


In my SQL Server 2008 R2 environment -


Created a 'source' and 'target' database and altered the database collation as follows -



use master
go
ALTER DATABASE source
COLLATE Chinese_PRC_CI_AS
GO

ALTER DATABASE target
COLLATE Chinese_PRC_CI_AS
GO


I created the below table in my source and target databases.



create table dbo.tcolcs1 (
c0 int not null primary key,

c1 varchar(30) collate SQL_Latin1_General_Cp1_CI_AS ,
c2 char(30) collate SQL_Latin1_General_Cp1_CI_AS ,
c3 text collate SQL_Latin1_General_Cp1_CI_AS ,
c4 nvarchar(30) collate SQL_Latin1_General_Cp1_CI_AS ,
c5 nchar(30) collate SQL_Latin1_General_Cp1_CI_AS ,
c6 ntext collate SQL_Latin1_General_Cp1_CI_AS ,

cA varchar(30) collate Chinese_PRC_CI_AS,
cB char(30) collate Chinese_PRC_CI_AS ,
cC text collate Chinese_PRC_CI_AS,
cD nvarchar(30) collate Chinese_PRC_CI_AS ,
cE nchar(30) collate Chinese_PRC_CI_AS,
cF ntext collate Chinese_PRC_CI_AS
)
go


I have inserted the below record in my source database.



insert into dbo.tcolcs1 values (10, nchar(0x00c4),nchar(0x00c4),'A',nchar(0x00c4), 'B', nchar(0x00c4),NCHAR(0x4EBC),NCHAR(0x4EBC),NCHAR(0x4EBC),NCHAR(0x4EBC),NCHAR(0x4EBC),NCHAR(0x4EBC))


Just for information, character '0x00c4' is available in Latin1 code page but does not exist in Chinese_PRC_CI_AS code page.


Once the SQL Server replication does its job, I found the below record in target database.



c0 c1 c2 c3 c4 c5 c6 cA cB cC cD cE cF
10 ? ? A Ä B Ä 亼 亼 亼 亼 亼 亼


Now, the problem is that SQL Server is not replicating the column C1 and C2 correctly. Both columns are having latin1 collation and character '0x00c4' exists in Latin code page.


Is this a limitation of SQL Server or any specific settings need to be enabled ?





Aucun commentaire:

Enregistrer un commentaire