在链接的MS Access表上插入后，SQL Server返回不同的记录 [英] SQL Server returns different record after insert on linked MS Access table
问题描述
我们最近将后端数据库从SQL Server 2000升级到了SQL Server2008.自从切换以来，我们遇到了间歇性问题(阅读:不可能始终如一地重现)和奇怪的问题，但是它们似乎都以某种方式相关.
在一种情况下，我们的用户通过绑定表格将新记录添加到表中. 记录保存后，将在其位置显示另一个(更旧的)记录.按 Shift + F9 强制重新查询表单的会带回新添加的记录(对表单进行过滤以仅显示单个记录).
我们已经设法根据发生在不同形式上的日志记录来隔离问题的特定实例.在窗体的BeforeUpdate事件中，在插入的记录上正确填写了时间戳.在相同形式的AfterUpdate事件中，将在另一个表中创建一个历史记录，其中包括第一个表的自动编号ID. 这些历史记录中大约有十分之一是使用错误的自动编号ID创建的.
有人见过这种行为或对此有任何解释吗?
其他想法:
- 后端数据库是合并复制的一部分
- Access前端版本为2000和2002(其他版本未经测试)
- 我读过的一篇文章建议Access在后台使用
@@IDENTITY从SQL Server取回新添加的记录 - 使用
{SQL Server}ODBC驱动程序和{SQL Server Native Client 10.0}ODBC驱动程序同时连接到后端表时会发生问题 - 兼容性级别设置为80(SQL Server 2000级别兼容性)
SQL事件探查器跟踪结果:
我运行了SQL事件探查器，并确认Access确实在后台使用SELECT @@IDENTITY返回新插入的记录.我确认这是在MS Access 2000、2002(XP)和2007前端中发生的.使用{SQL Server} ODBC驱动程序或{SQL Server Native Client 10.0} ODBC驱动程序链接表也正在发生.
我应该强调，Access在幕后使用SELECT @@IDENTITY .据我所知，没有办法强制Access使用SCOPE_IDENTITY.不过太糟糕了，因为这似乎是最简单的解决方法.
环顾四周(大多数情况下是garik包含在更多"中的链接)，表明您对这种行为不满意-这是一种访问/SQL Server通讯错误. 但是，在此链接中有解决方法.. >
对我来说，复制详细信息太复杂了，非常在这里得到了很好的解释，但是基本上，您将@@ IDENTITY保存到启动触发器中的变量中，然后进行虚假的#temp插入欺骗该值，使其返回到最后要返回的值.
We recently upgraded our backend database from SQL Server 2000 to SQL Server 2008. Since the switch we've had intermittent (read: impossible to consistently reproduce) and strange problems, but they all seem to be related somehow.
In one case, our users add a new record to a table via a bound form. As soon as the record is saved, a different (much older) record is displayed in its place. Pressing Shift+F9 to force a requery of the form brings back the newly added record (the form is filtered to show only a single record).
We have managed to isolate a specific instance of the problem based on logging that occurs on a different form. In the BeforeUpdate event of the form a timestamp is correctly filled in on the record being inserted. In the AfterUpdate event of the same form a history record is created in another table that includes the Autonumber ID of the first table. About 1 in 10 of these history records is created with the wrong Autonumber ID.
Has anyone witnessed this sort of behavior or have any explanation for it?
EDIT: Additional thoughts:
- the backend database is part of a merge replication
- the Access front-end versions are 2000 and 2002 (other versions not tested)
- one post I read suggested Access uses
@@IDENTITYbehind the scenes to get the newly added record back from SQL Server - the problem occurs using both the
{SQL Server}ODBC driver and the{SQL Server Native Client 10.0}ODBC driver to connect to the backend table - compatibility level is set to 80 (SQL Server 2000 level compatibility)
EDIT: SQL Profiler Trace results:
I ran SQL Profiler and confirmed that Access is indeed using SELECT @@IDENTITY behind the scenes to return the newly inserted record. I confirmed this is happening with MS Access 2000, 2002 (XP), and 2007 front-ends. It is also happening whether the tables are linked using the {SQL Server} ODBC Driver or the {SQL Server Native Client 10.0} ODBC driver.
I should emphasize that Access is using SELECT @@IDENTITY behind the scenes. As far as I know there is no way to force Access to use SCOPE_IDENTITY. Too bad, though, because that seems like it would be the simplest fix.
A bit of looking around (mostly off the link included as "more" by garik), shows that you're stuck with the behavior--it's an Access/SQL Server communication bug. However, there's a workaround described at this link.
It's way too complicated for me to reproduce in detail, and very well explained there, but basicly you save @@IDENTITY to variable at the start trigger, then do a phony #temp insert to spoof the value back to what you want returned at the end.
这篇关于在链接的MS Access表上插入后，SQL Server返回不同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
