Unexpected Remote Query Results
-
Why does:
Select count(*) from linked.server.table
Give me what I'd expect, but:
Select count(*) from (Select top 10 * from linked.server.table) t
Give me the same result
(I expected 10 or less)
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Now that's interesting... I didn't know about this behaviour, but you're right. The same happens when I try it on our server. I found something in BOL regarding use of linked analysis server, that seems to point to the reason of this strange result:
----------------------
Avoiding Four-Part Naming
It is possible to access the data of a cube directly from SQL Server using queries with four-part naming. (The four parts are linked-server-name, catalog, schema, and table.) However, this option is not recommended because SQL Server attempts to copy the contents of the entire fact table and then perform the calculations for aggregating the data itself, substantially increasing the query response time.
----------------------
It looks like the fact that all rows are copied before processing influences the result. I'm not sure whether this is it or there is some other reason, I'm just guessing... Anyway, you will get correct result if you use recommended way of executing SQL against linked server:
select count(*)
from OPENQUERY (linked_server, 'select top 10 * from db_name.owner.table_name')
-
I think you may have hit upon Microsofts buried disclaimer for this seemingly non-intuitive result...
My solution was to use OpenQuery() as you have mentioned, however, I had to bury it in dynamic SQL as its query parameter can only be a string constant, not a variable...
Thanks for replying. I feel better knowing it ain't just me
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
I just answered the question in the other thread. I did the same thing.
SELECT COUNT(*) FROM LinkedServer.LinkDB.dbo.Table
It gave me a big number.
Then I did
SELECT COUNT(*) FROM (SELECT TOP 10 * FROM LinkedServer.LinkDB.dbo.Table) t
I got 10.
It worked for me !
-
Hmmm,
That's disconcerting... I've tryed those two queries on 5 different servers (SQL2K SP3 on W2K Pro and 2003 Server) in-house and got the same wrong results each time. The only way I could get the correct results was if I used OPENQUERY(...).
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply