May 20, 2008 at 2:20 pm
I am getting a collation error on a simple join from two tables across linked server, the type and collation for the column in both tables is the same. One server is sql2000 sp3a and other is sql2005 sp2.
i have simplified statement as much as i could and still get error. Any help would be appreciated.
select * from [2000_table] a
join [linkedServer]. .dbo.[2005_table] b
on a.NID = b.NID
receive error
Cannot resolve collation conflict for equal to operation.
here is info from sp_help
2000_table: NID char no 15 yes no yes Latin1_General_BIN
2005_table: NID char no 15 no no no Latin1_General_BIN
May 21, 2008 at 2:56 am
Use COLLATE to tell SQL Server which collation to use.
E.g.:
select * from [2000_table] a
join [linkedServer]. .dbo.[2005_table] b
on a.NID = b.NID collate Latin1_General_BIN
...or:
select * from [2000_table] a
join [linkedServer]. .dbo.[2005_table] b
on a.NID collate Latin1_General_BIN = b.NID
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 21, 2008 at 8:19 am
May 21, 2008 at 2:51 pm
Check the settings for the linked servers and look up sp_serveroption in Books Online. Pay specific attention to the "collation compatible", "use remote collation" and "collation name" setting.
Depending on the actual environment linked servers can be setup in a way that would prevent collation exceptions using these linked server options.
Oh, and one more thing - test, re-test, double-test, and just to make sure - test again. 😉
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 22, 2008 at 8:18 am
thanks for the help. It was the linked server setting to use remote collation that was causing my conflict(it was set to false). once set to true, I no longer have to specify collation in my query.
May 22, 2008 at 10:03 am
Ok, but also consider the warning in Books Online regarding these settings. You don't want any unpredictable results, do you?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 22, 2008 at 12:48 pm
This problem came about because the remote server had been redeployed and the new linked server object I had created did not work in the same way as the old one (I didn't even think of checking the linked server settings). The remote collation setting on the old linked server object was set to true so if there are inconsistent results they were always there.
As for the 'warnings' in books online, the only two places I can see what would be a warning deals with the collation compatible and collation name settings. I have collation compatible set to false on collation name in blank. I only set the use remote collation setting to true.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply