October 12, 2009 at 12:30 am
We have our source database in sql 2000 and the target in sql 2008..we created a linked server to pull data from source and have set remote collation as true.But got the following error message:
OLE DB provider 'SQLNCLI10' for linked server <linkedservername> returned invalid data for column <tablename>.<columnname>
The particular column both in source and the target has Korean_Wansung_CI_AS and the target db has default collation.
When both the source and target server was in sql2000 we din face such issue.Are we missing something related to linked server properties.
EXEC master.dbo.sp_addlinkedserver @server = N'test', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @provstr=N'PROVIDER=SQLOLEDB;SERVER=sourceserver'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'test',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
October 12, 2009 at 1:34 am
Hi manu,
We tried that option also collation compatible=true but still the query fails with the same error.This happens particulary while entering currency symbols.
Thanks in advance
October 13, 2009 at 6:08 am
Can anyone suggest us on wyas to proceed
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply