Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Oct 15 '11 at 0:47

This question came from our site for professional and enthusiast programmers.

    
Can you confirm that you really do have [ and ] around the name? –  gbn Oct 15 '11 at 10:13
    
Absolutely. Only the largest of the tables fails when trying to do: SELECT * FROM [{dbname}.DATABASE.{organization}.COM].[{dbname}].[dbo].[{table}] (Again curly bracket values are just me not wanting to show our real values, and the name of the linked server is [{dbname}.DATABASE.{organization}.COM]) The error message omits the square brackets. It's weird, but true. –  OmegaX Oct 17 '11 at 12:56
add comment

1 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:

  1. Using OPENQUERY will allow you to use the linked server name as-is.
  2. (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
share|improve this answer
    
Synonyms aren't available in SQL Server 2000 –  Apocatastasis Aug 8 '13 at 18:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.