Help needed on SQL Server 2008 Linked Server issue

0

Hi All,

We are facing some issues with the Linked server created in SQL Server 2008. Kindly help us to resolve this issue.

Issue Description: - We are in process of migrating our application from SQL Server 2000 to SQL Server 2008. - Our application handles data that is in ‘Chinese language’ and the collation name is ‘Chinese_PRC_CI_AS’. - In SQL Server 2000, we are using Linked server (with the option use remote collation set to ‘TRUE’) to pull data from our source. - When we created and used the same linked server in SQL Server 2008 to pull records, we are faced with the following error OLE DB provider 'SQLNCLI10' for linked server 'CompassServer' returned invalid data for column '[CompassServer].[Compass].[dbo].[Table_Resrch_log].notes'. - The column Notes has the data type TEXT in both source and our DB and thereby we ensured that the schema is the same in both source and destination. - When we tried to set the option use remote collation to ‘FALSE’, we are able to pull the records without any issues but we are getting Junk characters where ever we have Chinese characters. - The same query that we used to pull records, works fine for almost 20000 records without any issues apart from 3 records where we find this error.

What we tried? - We tried to set the property use remote collation to ‘FALSE’, but if we do that, all the records with Chinese characters are pulled as Junk characters. - We also tried to type cast the data in the Select clause by doing a CONVERT(NText, Notes), but Type casting works row-by-row and this results in serious performance issue since the count of records that we pull is large. Moreover the data type of the column to be selected is Text and the performance could be worse hit if the number of records to be pulled increases. - We tried to Set value for Property ‘collation name’ to ‘Chinese_PRC_CI_AS’, but still the error persists. - We tried to Set value for Property ‘collation compatible’ to ‘TRUE’, but still the error persists.

Queries: - Why is the Linked Server created in SQL Server 2000 not throwing any issues or errors but the same Linked Server created in SQL Server 2008 throwing error while pulling records from the source? - Are there any other option or Property to be set explicitly while creating a Linked Server in SQL Server 2008. - Are there any patches available in SQL Server 2008 to avoid this issue? - Any work around to resolve this issue?

Sample Query that we use:

SELECT Notes
from CompassServer.Compass.dbo.Table_Resrch_log a
WHERE a.Objid in (1346021152, 1346022918, 1346025497);