SQL Server 2000 to SQL Server 2008 linked server issue Here's my scenario: I've got a linked server going from a SQL Server 2000 machine to a SQL Server 2008 machine. To aid in moving databases with a minimum of config changes, most of our connections point to [dbname.database.organization.com]. That is also the name of the linked server on the 2000 box. 

Most of the tables work fine. There is one exception: the biggest table of the bunch (450285 rows) gives this error (the values in the curly brackets have been changed from our real values): 


  An error occurred while executing batch.
  Error message is: Processing of results from SQL Server failed because 
  of an invalid multipart name
  "{DBNAME}.DATABASE.{ORGANIZATION}.COM.{DBNAME}.dbo.{TableName}", the
  current limit of "4" is insufficient.


I would expect that behavior if didn't have square brackets around the linked server name (which the error message is just omitting). The rest of the tables I need work just fine the exact same way.

I'm wondering if there is a maximum size setting I'm missing, etc.  

This application currently depends on a job that copies the entire table from the SQL Server 2008 machine to the SQL Server 2000 machine, nightly. I would really like to do away with that. 

Thanks!
I've had this problem before, and while I didn't find a fix (as it appears from the error message, SQL Server seems to 'lose' the brackets when it's processing the query), I did find a couple of workarounds:


Using OPENQUERY will allow you to use the linked server name as-is.
(My preferred solution): create a synonym for the linked server and use that in your query:

CREATE SYNONYM MyLinkedTable FOR [DBNAME.DATABASE.ORGANIZATION.COM].DBName.schema.TableName


Then use it in place of that huge mess:

SELECT * FROM MyLinkedTable