Geek Answers Handbook

SQL query giving invalid result on linked server

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

I reduced my request to

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

for testing and found that when I run it, I do not get any results, although I know that a 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 request and run it on the same server as the database, it works.

This seems to only affect certain user IDs, e.g.

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

works great for the user until the one I want.

Strange I found that

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

also works but

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

returns records with persIds starting from 22519, and not 475785, as I expected.

Hope this made sense to someone

Any ideas?

UPDATE: Due to internal problems related to making any changes to the table of live people, I temporarily moved my database so that they were on the same server, so the problem of a linked server does not apply. When the entire batch will be transferred to a separate cluster, I can correctly research. I will update the update as soon as this happens and I can work through all the suggestions. Thank you for your help.

+4
source share
5 answers

The fact that LIKE works is not the main key: LIKE casts integers to a string (so you can say that the WHERE field of LIKE is "2%" and you will get all records starting with 2, even if the field is of integer type ) Your incorrect comparisons will lead me to the idea that your indexes are corrupted, but you say that they work when not used by reference ... however, the selected index may differ depending on use? (It seems I recalled the instance when I had duplicate indexes, and only one was deprecated, although it was too long to remember the exact reason).

However, I would try to restore your index using the DBCC DBREINDEX (tablenname) command. If it turns out that this fixes your request, you can restore them all: here is a script for their full recovery.

+1
source

Is dbo.people a table or view? I saw something similar in which the basic layout of the table was changed, and deleting and re-creating the view fixed the problem, although the fact that the query works if it is executed directly on the linked server indicates that it is being indexed.

+1
source

I would check the following:

  • Test your definition on a linked server and make sure SQL2 is the server you expect it to be
  • Check and compare execution plans from both remote and local server
  • Try binding the IP address, not the name, to make sure you have the appropriate machine.
  • Put the code in a stored procedure on the remote computer and try calling instead
0
source

It sounds like a mistake to me - I read about some problems in this direction, btu does not remember what exactly. What version of SQL Server are you using?

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

for PersID that doesn't work:

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

to lead?

0
source

Is the linked server connected to the same sort? Depending on the index used, I could see that this happens, possibly when the servers are not compatible with each other, but the linked server is configured for compatibility with the mapping (which tells Sql Server that it can run the request on the remote server).

0
source

More articles:


All Articles