From: RickSpicknall on
3 days ago I switched out my old SQL 2005 (SQL_Latin1_General_CP1_CI_AS)
server for a new one with SQL 2008(SQL_Latin1_General_CP1_CI_AS). I created
the Linked Server to my second server that runs SQL 2005 (Latin1_General_BIN)
with the default Linked Server settings. When trying to compile stored
procedures from the new SQL 2008 server that calls objects on the second SQL
2005 server I was getting an error (Cannot resolve the collation conflict
between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal
to operation) I found that SQL 2008 by default is setting the Linked Server
option “Use Remote Collation” to True. This is not the default in SQL 2005.
So that problem solved, I can now compile the stored procedures, however, now
on the new SQL 2008 server all my stored procedures that reference the SQL
2005 server, I get syntax/intellisense error “Invalid object name ServeName”,
but the stored procedures do compile without errors and are working ok.
There are a few new options in the Linked Server options, but I am not sure
exactly if that is the cause. Any incite? Thanks in advance.