x

Help needed on SQL Server 2008 Linked Server issue

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);                    

Kindly help us resolve this issue.

Thanks & Regards,
Raja Sundaram

more ▼

asked Feb 08, 2010 at 06:17 AM in Default

Raja Sundaram gravatar image

Raja Sundaram
1 1 1 1

Can you not use nvarchar(max) or is your field too large for this?
Feb 08, 2010 at 08:59 AM RickD

but the select part itself throws error.

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

this select part itself throws error. So changing to nvarchar(max) in the DB doesn't help in anyways.
Feb 08, 2010 at 09:42 AM Raja Sundaram

So, does the following throw an error?

SELECT convert(nvarchar(max),Notes) from CompassServer.Compass.dbo.Table_Resrch_log a WHERE a.Objid in (1346021152, 1346022918, 1346025497);
Feb 08, 2010 at 11:43 AM RickD
Thanks Rick for your inputs. Yes. the Select query throws the following error "OLE DB provider 'SQLNCLI10' for linked server 'CompassServer' returned invalid data for column '[CompassServer].[Compass].[dbo].[Table_Resrch_log].notes'". Any inputs to resolve this linked server issue.
Feb 09, 2010 at 12:20 AM Raja Sundaram
(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

Hi,
I am also facing the similar issue. Any update on this?

more ▼

answered Apr 27, 2010 at 05:22 AM

Sivaprakash gravatar image

Sivaprakash
1

(comments are locked)
10|1200 characters needed characters left

Hi,

I am trying to connect to server but i give me network related or instance-specific error,

(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: )

all ports are open on server firewall. all services are running except SQL server Agent is stopped. i can ping the server ip address.

please help me? Any suggestion will be highly apprecaited.

waiting for your kind response.

more ▼

answered Jun 26, 2010 at 04:03 PM

Nasir Safekom gravatar image

Nasir Safekom
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x106

asked: Feb 08, 2010 at 06:17 AM

Seen: 2464 times

Last Updated: Feb 08, 2010 at 11:12 AM