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

StackExchange Database Administrators Q#6839, answer score: 3