3

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!

Improve this question
2
  • Can you confirm that you really do have [ and ] around the name?
    – gbn
    Commented Oct 15, 2011 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
    Commented Oct 17, 2011 at 12:56

1 Answer 1

Reset to default
3

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
Improve this answer
1
  • Synonyms aren't available in SQL Server 2000
    – Apocatastasis
    Commented Aug 8, 2013 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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