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.
我试图从2个表中提取用户数据，一个在本地，一个在链接服务器上，但是在查询远程服务器时我得到了错误的结果。
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)
为了测试，发现当我运行它时，即使我知道这个人存在，我也没有得到任何结果。 （persId是一个整数，db是SQL Server 2000，dbo.people就是一个表）
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
例如，它似乎只会影响某些用户ID
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.
根据我的预期，从22519而不是475785开始带回记录。
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.
更新：由于内部关注对live people表进行任何更改，我暂时移动了我的数据库，因此它们都在同一台服务器上，因此链接服务器问题不适用。将整批产品迁移到一个单独的集群后，我将能够正确调查。一旦发生这种情况，我会更新更新，我可以通过所有建议。谢谢你的帮助。
5 个解决方案
#1
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).
LIKE操作的事实不是一个主要线索：LIKE强制整数字符串（所以你可以说WHERE字段LIKE'2％'并且你将得到所有以2开头的记录，即使字段是整数类型）。您的错误比较会让我认为您的索引已损坏，但您说它们在未通过链接使用时有效...但是，根据使用情况，所选索引可能会有所不同？ （我似乎记得一个实例，当我有重复的索引，只有一个是陈旧的，虽然那是很久以前回想起确切的原因）。
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.
不过，我会尝试使用DBCC DBREINDEX（tablenname）命令重建索引。如果事实证明这样做会修复你的查询，你可能想要重建它们：这里有一个脚本，可以很容易地重建它们。
#2
1
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..
dbo.people是桌子还是视图？我已经看到类似的地方已经更改了基础表模式，删除和重新创建视图修复了问题，尽管如果直接在链接服务器上运行查询工作的事实确实表明基于索引的东西..
#3
0
I would check the following:
我会检查以下内容：
- Check your definition on the linked server, and confirm that SQL2 is the server you expect it to be
- 检查链接服务器上的定义，并确认SQL2是您期望的服务器
- 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
- 尝试通过IP地址而不是名称进行链接，以确保您拥有合适的机器
- Put the code into a stored procedure on the remote machine, and try calling that instead
- 将代码放入远程计算机上的存储过程中，然后尝试调用它
#4
0
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).
链接服务器是否使用相同的排序规则？根据所使用的索引，如果服务器不兼容排序规则，我可能会看到类似这样的事情，但链接服务器设置了兼容的排序规则（告诉Sql Server它可以在远程服务器上运行查询）。
#5
0
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?
对我来说听起来像个错误 - 我已经读过这些问题，btu不记得具体是什么。你在运行什么版本的SQL Server？
select * from SQL2.USER.dbo.people where persId = 475785
for a PersID which fails how does:
对于PersID失败的方法如何：
SELECT *
FROM OpenQuery(SQL2, 'SELECT * FROM USER.dbo.people WHERE persId = 475785')
behave?
表现？
#1
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).
LIKE操作的事实不是一个主要线索：LIKE强制整数字符串（所以你可以说WHERE字段LIKE'2％'并且你将得到所有以2开头的记录，即使字段是整数类型）。您的错误比较会让我认为您的索引已损坏，但您说它们在未通过链接使用时有效...但是，根据使用情况，所选索引可能会有所不同？ （我似乎记得一个实例，当我有重复的索引，只有一个是陈旧的，虽然那是很久以前回想起确切的原因）。
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.
不过，我会尝试使用DBCC DBREINDEX（tablenname）命令重建索引。如果事实证明这样做会修复你的查询，你可能想要重建它们：这里有一个脚本，可以很容易地重建它们。
#2
1
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..
dbo.people是桌子还是视图？我已经看到类似的地方已经更改了基础表模式，删除和重新创建视图修复了问题，尽管如果直接在链接服务器上运行查询工作的事实确实表明基于索引的东西..
#3
0
I would check the following:
我会检查以下内容：
- Check your definition on the linked server, and confirm that SQL2 is the server you expect it to be
- 检查链接服务器上的定义，并确认SQL2是您期望的服务器
- 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
- 尝试通过IP地址而不是名称进行链接，以确保您拥有合适的机器
- Put the code into a stored procedure on the remote machine, and try calling that instead
- 将代码放入远程计算机上的存储过程中，然后尝试调用它
#4
0
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).
链接服务器是否使用相同的排序规则？根据所使用的索引，如果服务器不兼容排序规则，我可能会看到类似这样的事情，但链接服务器设置了兼容的排序规则（告诉Sql Server它可以在远程服务器上运行查询）。
#5
0
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?
对我来说听起来像个错误 - 我已经读过这些问题，btu不记得具体是什么。你在运行什么版本的SQL Server？
select * from SQL2.USER.dbo.people where persId = 475785
for a PersID which fails how does:
对于PersID失败的方法如何：
SELECT *
FROM OpenQuery(SQL2, 'SELECT * FROM USER.dbo.people WHERE persId = 475785')
behave?
表现？