February 8, 2010 at 2:05 am
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 (xxxx);
Kindly help us resolve this issue at the earliest.
Thanks & Regards,
Raja Sundaram
February 8, 2010 at 12:01 pm
I would suggest altering the text column on your new 2008 server to be nvarchar(max). Text will no longer be supported beyond version 10 anyway so you may as well bite the bullet.
Alter table xxyyzz alter column textColumn nvarchar(MAX)
The probability of survival is inversely proportional to the angle of arrival.
February 8, 2010 at 9:40 pm
Hi,
Thanks for your reply and inputs. We tried converting the data type in our DB to nvarchar(max) but we dont have control over the source DB. there the data type is TEXT.
Moreover the error occurs in the Select Clause itself. While selecting records from source itself, we are faced the the error.
The following Select Stmt throws error,
SELECT Notes from CompassServer.Compass.dbo.Table_Resrch_log a WHERE a.Objid in (1346021152, 1346022918, 1346025497);
Error msg:
"OLE DB provider 'SQLNCLI10' for linked server 'CompassServer' returned invalid data for column '[CompassServer].[Compass].[dbo].[Table_Resrch_log].notes'"
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply