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.
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`.