SQL query giving wrong result on linked server

Question

Hi

I'm trying to pull user data from 2 tables, one locally and one on a linked server, but I get the wrong results when querying the remote server.

I've cut my query down to

select * from SQL2.USER.dbo.people where persId = 475785

for testing and found that when I run it I get no results even though I know the person exists. (persId is an integer, db is SQL Server 2000 and dbo.people is a table by the way)

If I copy/ paste the query and run it on the same server as the database then it works.

It only seems to affect certain user ids as running for example

select * from SQL2.USER.dbo.people where persId = 475784

works fine for the user before the one I want.

Strangely I've found that

select * from SQL2.USER.dbo.people where persId like '475785'

also works but

select * from SQL2.USER.dbo.people where persId > 475784

brings back records with persIds starting at 22519 not 475785 as I'd expect.

Hope that made sense to somebody

Any ideas ?

UPDATE: Due to internal concerns about doing any changes to the live people table, I've temporarily moved my database so they're both on the same server and so the linked server issue doesn't apply. Once the whole lot is migrated to a separate cluster I'll be able to investigate properly. I'll update the update once this happens and I can work my way through all the suggestions. Thanks for your help.

Answer 1

The fact that LIKE operates is not a major clue: LIKE forces integers to string (so you can say WHERE field LIKE '2%' and you will get all records that start with a 2, even when field is of integer type). Your incorrect comparisons would lead me to think your indexes are corrupt, but you say they work when not used via the link... however, the selected index might be different depending on the use? (I seem to recall an instance when I had duplicate indexes and only one was stale, although that was too long ago to recall the exact cause).

Nevertheless, I would try rebuilding your index using the DBCC DBREINDEX (tablenname) command. If it turns out that doing so fixes your query, you may want to rebuild them all: here is a script for rebuilding them all easily.

Answer 2

Is dbo.people a table or a view? I've seen something similar where the underlying table schema had been changed and dropping and recreating the view fixed the problem, although the fact that the query works if run directly on the linked server does indicate something index based..

Answer 3

I would certainly follow Godeke advice and rebuild the index on the table on the linked server then report back to see if it made any difference.

Also try viewing the Execution plan for the SQL on the linked server (where it works) and on the other server (where it doesnt) to see what index's it is using in case you missed any index's that need rebuilding.

Answer 4

I would check the following:

• Check your definition on the linked server, and confirm that SQL2 is the server you expect it to be
• Check and compare the execution plans both from the remote and local servers
• Try linking by IP address rather than name, to ensure you have the proper machine
• Put the code into a stored procedure on the remote machine, and try calling that instead

Answer 5

Sounds like a bug to me - I;ve read of some issues along these lines, btu can't remember specifically what. What version of SQL Server are you running?

select * from SQL2.USER.dbo.people where persId = 475785

for a PersID which fails how does:

SELECT *
FROM OpenQuery(SQL2, 'SELECT * FROM USER.dbo.people WHERE persId = 475785')

behave?

Answer 6

Is the linked server using the same collation? Depending on the index used, I could see something like this perhaps happening if the servers were not collation compatible, but the linked server was set up with collation compatible (which tells Sql Server it can run the query on the remote server).