question

WilliamD avatar image
WilliamD asked

Problems with a linked server connection between 2005 and 2008R2

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: - Server A = SQL Server 2005 SP3 - Server B = SQL Server 2008 R2 RTM 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: Msg 4012, Level 16, State 1, Line 1 An invalid tabular data stream (TDS) collation was encountered 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.
sql-server-2005sql-server-2008-r2linked-server
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
2 Likes 2 ·
no network police between the two?
1 Like 1 ·
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.
1 Like 1 ·
@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.
0 Likes 0 ·
Another good idea @Oleg - this place confirms itself on a daily basis!
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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 `Latin1_General_100_CI_AS` didn't play nice with `Latin1_General_CI_AS`.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
5 Likes 5 ·
collation was mentioned in the error message. Foolish me for taking someone's word for it! ;-)
0 Likes 0 ·
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.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
One thought: it's a data type issue - if the table being queried on the SQL2008R2 box contains a datatype that's not supported by SQL2005....
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Thomas - great minds think alike. The colleague got back to me this morning and said that a select against sys.objects worked, but not on a certain table. I'm getting them to check the table for a "new" data type. Great catch there! Will tick once it is confirmed.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.