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.
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
answered Jan 20 '11 at 02:35 AM
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....
answered Jan 20 '11 at 12:54 AM