|
A former colleague of mine is having a little trouble with a linked server connection and I am stumped at the moment. The setup is:
Both servers have a linked server connection to the other machine. SSMS can display all objects in the corresponding databases on both machines when looking through the object explorer A select from Server B against Server A works fine A select from Server A against Server B fails with the error: Both machines are identical in terms of collation, their linked server settings are also identical. the only difference is 2005 & 2008R2. Does anyone know why this error is occuring? Is the native client so different between 2005 and 2008R2 that it breaks here? I myself have had no problems with compatibility between 2k, 2k5 and 2k8 so cannot understand this at all. Any help greatly appreciated.
(comments are locked)
|
|
Well I managed to answer this one myself it seems. The problem was collation as the error mentioned. The system collations were matched, but the database collations were not. As soon as that was sorted out, the Linked Server query ran without any errors. The collation collation was mentioned in the error message. Foolish me for taking someone's word for it! ;-)
Jan 20 '11 at 02:55 AM
ThomasRushton ♦
Yes, but I did ask them if collation was identical (I even said so in the question). I reckon your answer was the next best guess - the information given to me was lacking. I had to point out the machine was 2k8R2 not just 2k8 - which then got me thinking about the data types as you answered.
Jan 20 '11 at 02:57 AM
WilliamD
Collations are a bit of a PITA... server-level, database-level, and you can override on a column. All there to snare the unwary DBA or developer.
Jan 20 '11 at 03:14 AM
ThomasRushton ♦
(comments are locked)
|
no network police between the two?
If SSMS displays all objects in the explorer correctly then it probably means that the big brother is not the one to blame yet. Does any select fail or it is possible to craft one which will work? For example, trying to select some_non_decimal columns from the_table or better select some_varchar_columns could provide a good hint.
It kinda smells that the RTM would be the key here. Is it at all possible to patch it to become a real installation and try again?
I see a bunch of links on google pointing to some Russian SQL sites (no answers though, just an identical question). Them people are probably still using the outdated RTM extensively. This really hints that RTM could be the one to blame here.
@Kev - I asked about the possibility - not confirmed yet, but unlikely.
@Oleg - as far as I am informed, no selects work at all. I tend to think it could be an RTM thing too, as the problem is reproducable from other 2k5 machines in their network. I will ask what patching strategy is possible to see if that will fix it.
@WilliamD Did they try to restate their select queries as from openrowset? Linked servers are convenient because of the 4-part names is all what is needed to query data across the servers, but openrowset with server name plus 3-part object names is not a bad choice either. Trying it can also help to isolate the problem somewhat.
Another good idea @Oleg - this place confirms itself on a daily basis!