CODEWITHSUNDEEP
sqlsql-server
excalibur
excalibur

Reputation: 611

SQL query giving wrong result on linked server

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.

Upvotes: 4

Views: 4253

Answers (5)

Kristen
Kristen

Reputation: 4301

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?

Upvotes: 0

Mark Brackett
Mark Brackett

Reputation: 85665

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).

Upvotes: 1

Noah
Noah

Reputation: 15330

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

Upvotes: 0

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

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..

Upvotes: 1

Godeke
Godeke

Reputation: 16281

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.

Upvotes: 1

Related Questions