I struggled for quite a little while trying to execute this:
Select top 1 * From [xxx.xxx.xxx.xxx].[db].[owner].[table]
In SQL2005 Management Studio against a SQL2000 box.
And receiving this:
An error occurred while executing batch. Error message is: Processing of results from SQL Server failed because of an invalid multipart name "xxx.xxx.xxx.xxx.db.owner.table", the current limit of "4" is insufficient.
I was unable to link the servers by name since there was no DNS to resolve it.
I finally discovered that I didn't receive the error if I simply used SQL 2000's Query analyzer. I was a little surprised to find that this issue in backwards compatibility existed in the Management Studio. I have not had a chance to test a SQL 2005 linked server to see if an IP address causes the same issue on it, though I would hope that it wouldn't.
Note: I just discovered that I was able to use OPENQUERY in Management Studio to run my query from one SQL2000 server to a linked SQL2000 server.
SELECT * FROM OPENQUERY([xxx.xxx.xxx.xxx], 'SELECT * FROM [db].owner.[table]') AS tablename
A collection of problems and discoveries usually related to the tech industry in some way.
3 comments:
Thank you very much. It works.
I had this issue with SQL2005 to SQL2005 link.
Even Microsoft did not have any recommendation for this error in any obvious area of MSDN.
By the way, how did you discover OPENQUERY?
Thanks again.
Thank you, thank you, thank you!
I do not recall what prompted the OpenQuery idea. I have had enough experience with SQL Server to know that simply changing the way a query is written can drastically change the results. It was probably just a random thought that popped into my head.
Post a Comment