x

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.
more ▼

asked Jan 19, 2011 at 08:06 AM in Default

WilliamD gravatar image

WilliamD
25.9k 17 19 41

no network police between the two?
Jan 19, 2011 at 08:49 AM Kev Riley ♦♦

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.
Jan 19, 2011 at 09:20 AM Oleg

@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.
Jan 19, 2011 at 10:38 AM WilliamD
@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.
Jan 19, 2011 at 10:51 AM Oleg
Another good idea @Oleg - this place confirms itself on a daily basis!
Jan 19, 2011 at 01:07 PM WilliamD
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Jan 20, 2011 at 02:35 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

collation was mentioned in the error message. Foolish me for taking someone's word for it! ;-)
Jan 20, 2011 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, 2011 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, 2011 at 03:14 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
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....
more ▼

answered Jan 20, 2011 at 12:54 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

@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.
Jan 20, 2011 at 01:46 AM WilliamD
(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:

x1945
x582
x107

asked: Jan 19, 2011 at 08:06 AM

Seen: 5162 times

Last Updated: Jan 20, 2011 at 04:54 AM