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!
Best Answer
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:
(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: