Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maxjam
Starting Member
9 Posts |
 Posted - 2010-07-20 : 04:48:45
|
Hi all,I'm getting an error and can find no obvious cause.The following query:SELECT TOP 10 *FROM [10.100.200.300\Server].DatabaseName.dbo.ABCCustomerAccounts gets this error:An error occurred while executing batch. Error message is: Processing of results from SQL Server failed because of an invalid multipart name "10.100.200.300\Server.DatabaseName.dbo.ABCCustomerAccounts", the current limit of "4" is insufficient.i figured that maybe the full linked server and table definition string was too long but tried a longer table name from the same database:SELECT TOP 10 *FROM [10.100.200.300\Server].DatabaseName.dbo.ABC_Large_Transactions_Archive however this returns a result set!!! Totally stumped... has anyone any idea why this may be happening? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-20 : 04:51:44
|
Weird!Longshot: ABCCustomerAccounts is a view on that database refering to something remote-from-it perhaps?This any better?SELECT *FROM OPENQUERY([10.100.200.300\Server],'SELECT TOP 10 *FROM DatabaseName.dbo.ABCCustomerAccounts') (I presume it isn't really ".300" which is a bit on the "high" side  ) |
 |
|
|
maxjam
Starting Member
9 Posts |
Posted - 2010-07-20 : 05:01:34
|
| no thats the thing... its actually a full normal table!obviously i didnt put in the proper names for IPs and servers etc!OPENQUERY works fine, Im just wondering why the regular query works happily for one table and not another!? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-20 : 05:24:47
|
In your first post you have shown two statements.The first statement is giving an error.The second statement is running fine.My question is:Have you tried both statements in a SSMS Query Window?Or was the failing query running in a batch outside SSMS?I am asking this because it may be possible the SSMS can handle the square brackets and outside SSMS it will not work.If so then the solution will be to give a name to the linked server instead of using the IP.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
maxjam
Starting Member
9 Posts |
Posted - 2010-07-20 : 05:56:41
|
| The statement that fails actually works fine as part of a stored procedure (selecting into a temp table) but when executed on its own it fails.The problem is not that my work cannot proceed but more that im trying to understand why this happens when you pull the statement into a query window on its own. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-20 : 06:53:23
|
| Is there no difference in the[10.100.200.300\Server].DatabaseName.dbo.bit between the query that works, and the one that does not?The one that fails is not formed from dynamic SQL or somesuch that might result in a subtlety different command? (character collation, a character that LOOKS right but is actually a different ASCI value, etc) |
|
|
|
rammohan
Posting Yak Master
212 Posts |
Posted - 2010-12-03 : 04:15:07
|
| i am using the openquery method, but i need to send a parametr to inside sql. i am trying like this :declare @AppName varchar(10)set @AppName = 'xyz'select * from openquery([111.12.12.112],'select top 1 * FROM test where name = @AppName') I am getting following error:Must declare the variable '@AppName'.Please suggest me on thisOne can never consent to creep,when one feels an impulse to soarRAMMOHAN |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-03 : 07:06:23
|
| Start a new thread please |
|
|
|
|
|
|
|
|
Strange error when selecting from linked server