在链接的 MS Access 表上插入后，SQL Server 返回不同的记录

Question

我们最近将后端数据库从 SQL Server 2000 升级到 SQL Server 2008。自从切换以来，我们遇到了间歇性（读取：不可能一致重现）和奇怪的问题，但它们似乎都以某种方式相关。

在一种情况下，我们的用户通过绑定表单向表添加一条新记录。 保存记录后，就会在其位置显示不同的（更旧的）记录。按 Shift+F9 强制重新查询表单的 会返回新添加的记录（表单被过滤以仅显示单个记录）。

我们已经成功地根据不同表单上发生的日志记录来隔离问题的特定实例。在表单的 BeforeUpdate 事件中，在插入的记录上正确填写了时间戳。在同一表单的 AfterUpdate 事件中，会在另一个表中创建历史记录，其中包含第一个表的自动编号 ID。 大约十分之一的历史记录是使用错误的自动编号 ID 创建的。

有人目睹过这种行为或对此有任何解释吗？

编辑：其他想法：

• 后端数据库是合并复制的一部分，
• Access前端版本是2000和2002（其他版本未测试）
• 我读过的一篇文章建议Access使用@@IDENTITY 在后台从 SQL Server 获取新添加的记录时，
• 使用 {SQL Server} ODBC 驱动程序和 {SQL Server Native Client 10.0} 会出现问题code> 连接到后端表的 ODBC 驱动程序
• 兼容性级别设置为 80（SQL Server 2000 级别兼容性）

编辑： SQL Profiler 跟踪结果：

我运行 SQL Profiler 并确认 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。但太糟糕了，因为这似乎是最简单的解决办法。

Answer 1

使用 SCOPE_IDENTITY 而不是 @@IDENTITY。

由于 @@IDENTITY 返回最后一个
当前生成的身份值
会话中，如果有一些触发器
当前操作的任何表
会话中，我们会得到意想不到的价值。
为了得到需要的值，
请使用 SCOPE_IDENTITY。这
函数将返回插入的值
仅限当前范围内。

更多

Answer 2

环顾四周（主要是从 garik 包含的“更多”链接中删除），表明您陷入了这种行为——这是一个 Access/SQL Server 通信错误。 但是，此链接中描述了一种解决方法。

这对我来说太复杂了，无法详细重现，而且非常在那里解释得很好，但基本上你在开始触发器时将 @@IDENTITY 保存到变量，然后做一个假的#temp 插入以将值欺骗回您想要最后返回的值。

Answer 3

哇！那是多么痛苦的经历啊。首先，向 MS Access 团队简要说明一下：

如果您尚未对 A2010 执行此操作，请花五分钟时间在代码库上进行查找和替换，将 SELECT @@IDENTITY 的每个实例替换为 SCOPE_IDENTITY() 与 SQL Server 的接口。这是一个很好的例子，说明开源项目很久以前就已经被修复了……但我离题了。




对于这篇非常冗长的文章，我先表示歉意。我将尝试记录过去一周所获得的所有知识，试图解决这个棘手的问题。我原来的帖子总结了我所看到的行为。请先阅读该内容以获取完整的上下文。

最适合我解决这个问题的是它看似随机的性质。我们会进行插入，但它会失败。然后我们再做五十个，他们都会成功。然后第二天我们再做一次插入，结果又失败了。然后我们再做十次，他们都会成功。然后几个小时后又会失败。不断地。

该问题是由合并复制引起的。当表作为项目添加到合并复制发布时，会自动生成多个触发器来管理复制。当我们在 SQL Server 2000 中使用合并复制时，这对我们来说不是问题。但是，从 SQL Server 2005 开始，这些触发器被修改了。导致问题的具体修改在于插入中自动生成的这些代码行受影响表的触发器：

select @newgen = NULL
    select top 1 @newgen = generation from [dbo].[MSmerge_genvw_8D1ADB4453634BF39DA4AA582FE18F78] with (rowlock, updlock, readpast) 
    where art_nick = 14201004    and genstatus = 0
        and  changecount <= (1000 - isnull(@article_rows_inserted,0))
if @newgen is NULL
begin
    insert into [dbo].[MSmerge_genvw_8D1ADB4453634BF39DA4AA582FE18F78] with (rowlock)
        (guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
         values   (newid(), 0, @tablenick, @nickbin, @dt, @article_rows_inserted)
    select @error = @@error, @newgen = @@identity    
    if @error<>0 or @newgen is NULL
        goto FAILURE
end
else
begin
    -- now update the changecount of the generation we go to reflect the number of rows we put in this generation
    update [dbo].[MSmerge_genvw_8D1ADB4453634BF39DA4AA582FE18F78]  with (rowlock)
        set changecount = changecount + @article_rows_inserted
        where generation = @newgen
    if @@error<>0 goto FAILURE
end

以下是上面代码片段中发生的情况。 SQL Server 正在检查 MSmerge_genhistory 表（MSmerge_genvw_8D1ADB4453634BF39DA4AA582FE18F78 是该表的系统视图）。如果存在打开行 (genstatus = 0)，并且插入次数加上更改计数不超过 1000，则计数器会递增。 但是，如果没有打开的行，则会插入新行。这会重置 @@IDENTITY 变量。集体歇斯底里随之而来。猫和狗，住在一起。等等。

需要明确的是，这里的错误在于使用@@IDENTITY的Access团队，而不是修改合并复制内部的SQL Server团队。但是，天啊，我以为你们在为同一支球队效力……

值​​得注意的是，合并复制涉及的每个表都有不同的行。我发现以下查询对于故障排除和了解 genhistory 表中发生的情况最有帮助：

SELECT A.name
      ,H.generation
      ,H.art_nick
      ,H.coldate
      ,H.genstatus
      ,H.changecount
  FROM [MSmerge_genhistory] AS H
  INNER JOIN [sysmergearticles] AS A
  ON H.art_nick = A.nickname
  ORDER BY H.generation DESC

那么什么关闭了 genhistory 表中的一行？嗯，除其他外，每当合并代理针对数据库运行时，genhistory 表中的每一行都会关闭。任何合并代理。对于任何出版物。在我们的例子中，我们有两个独立的合并复制发布，它们在同一个数据库中运行。一个合并代理每小时运行一次；另一个每晚运行。

这让我们回到了看似随机的行为。我将注释我之前的段落来解释该行为：

我们会进行插入，但它会失败。 [在 genhistory 表中插入新行。] 然后我们再做五十次，他们都会成功。 [genhistory 表中的行递增。] 然后第二天 [在每晚（和每小时）合并代理运行并关闭 genhistory 表中的行之后] 我们将执行以下操作：插入，又会失败。 [在 genhistory 表中插入新行。] 然后我们再做十次，他们都会成功。 [genhistory 表中的行增加。] 然后几个小时后 [每小时合并代理运行并关闭 genhistory 表中的行] 会再次失败。 [在 genhistory 表中插入新行。]

现在我们终于知道发生了什么，我们需要一些方法来修复它。 “正确”的方法是使用 SCOPE_IDENTITY() 而不是 SELECT @@IDENTITY。然而，这种行为被硬编码到 MS Access 中，因此我们不得不在 SQL Server 中采取解决方法。

第三种解决方法似乎是最好的选择，但它需要多次关闭和重新启动整个数据库服务器以及作者自己的额外警告：

最后的话（免责声明）： 正如我已经说过的，Microsoft 完全不支持此修补程序。对于因使用或无法使用本文档或其中包含的任何材料、或因任何行动或决定而造成的任何损害（包括但不限于业务损失或利润损失），我不承担任何责任由于使用本文件或任何此类材料而得出。我在我的环境中测试了这个过程，解决了问题并按预期工作。我鼓励您首先在虚拟化/测试环境中进行自己的测试。在应用将来可能出现的任何新的 SQL Server Service Pack 之前，我建议停止服务器，恢复我们在过程步骤 1 中复制的原始文件，然后按照 SP 的说明进行操作。应用 SP 后（也适用于订阅者），您可以重新初始化发布并查看是否再次出现 MS Access 错误。如果是这样，我想您可以重新修补 SP 可能已设置的新资源数据库。

....因此，在启动和停止整个数据库服务器之后，我需要对核心 SQL Server 行为进行一些不受支持的更改，并记住在应用未来的服务包之前取消这些更改并重新应用它们。嗯，不用了，谢谢。

我重新阅读了这篇文章几次，意识到关键信息是 这个技巧可以保存和恢复@@IDENTITY值。我意识到我需要做的就是将这四行应用到每个合并插入触发器：

declare @identity int, @strsql varchar(128) 
set @identity=@@identity 

set @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',1) as id into #tmp' 
execute (@strsql)

作者的方法是修改基本合并插入触发器，以便使用已经就位的上述行创建自动生成的触发器。这仅涉及编辑一处，但具有已经提到的缺点。我的方法是在创建触发器后对其进行修改。唯一的缺点是您必须在更多地方执行此操作（即每个表一次）。但如果你能以某种方式编写脚本......

让这一切发挥作用的最后一个难题是弄清楚当前所有的触发器是什么。这需要使用两个系统视图：triggers 和 syscomments。我使用 triggers 视图来识别有问题的触发器（它们的名称均以“MSmerge_ins”开头）。然后，我使用 syscomments 视图获取用于创建每个触发器的 T-SQL。 syscomments.text 字段的大小为 4000。如果 T-SQL 超过 4000 个字符，它将被分成按 syscomments.colid 排序的多行。

我的最终算法如下：

1. 循环遍历每个“MSMerge_ins”触发器
2. 从 syscomments 视图重建 CREATE TRIGGER T-SQL
3. 通过正则表达式运行 CREATE TRIGGER T-SQL
4. 如果需要修改触发器，则正则表达式返回 ALTER TRIGGER 语句
5. 如果触发器已被修改，则正则表达式返回未更改的 T-SQL
6. 如果返回 ALTER TRIGGER 语句，则它将作为修改触发器的传递查询执行

每当我创建（或重新创建）合并复制发布时，我仍然必须记住运行此代码。我的方法的另一个缺点是我的正则表达式可能需要修改以处理自动生成的触发器的未来更改。但我可以在实时服务器上做到这一点，我不必关闭任何东西，而且我也不会担心我可能会破坏核心功能。自己决定什么可以忍受，什么不能忍受。

我写这个是为了在 MS Access 中运行。为了简化代码，我创建了指向 triggers、syscomments 和 sys_tables 视图的链接。 sys_tables 视图并不是绝对必要的，但我将其保留用于调试。

这是代码：

Sub FixInsertMergeTriggers()
Dim SQL As String, TriggerSQL As String, AlterSQL As String
Dim PrevTrigger As String, ProcessTrigger As Boolean

    SQL = "SELECT Ta.Name AS TblName, Tr.Name AS TriggerName, " & _
          "       C.Text, C.Number, C.colid " & _
          "FROM (syscomments AS C " & _
          "INNER JOIN triggers AS Tr ON C.id=Tr.object_id) " & _
          "INNER JOIN sys_tables AS Ta ON Tr.parent_id=Ta.object_id " & _
          "WHERE Tr.name like 'MSmerge_ins*' " & _
          "ORDER BY Tr.name, C.colid"

    With CurrentDB.OpenRecordset(SQL)
        Do
            If .EOF Then
                If Len(PrevTrigger) > 0 Then
                    ProcessTrigger = True
                Else
                    Exit Do
                End If
            Else
                ProcessTrigger = (!TriggerName <> PrevTrigger)
            End If
            If ProcessTrigger Then
                If Len(TriggerSQL) > 0 Then
                    AlterSQL = ModifyTrigger(TriggerSQL)
                    If AlterSQL <> TriggerSQL Then
                        ExecPT AlterSQL
                        Debug.Print !TblName; " insert trigger altered"
                    End If
                End If
                TriggerSQL = ""
                If .EOF Then Exit Do
            End If
            TriggerSQL = TriggerSQL & !Text
            PrevTrigger = !TriggerName
            .MoveNext
        Loop
    End With
    Debug.Print "Done."
End Sub

Private Function ModifyTrigger(TriggerSQL As String) As String
Const DeclarationSection As String = "    declare @identity int, @strsql varchar(128)" & vbCrLf & _
                                     "    set @identity=@@identity"
Const ExecuteSection As String = "    set @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',1) as id into #tmp'" & vbCrLf & _
                                 "    execute (@strsql)"
Dim P As String  'variable that holds our regular expression pattern'

    'Use regular expression to modify the trigger'
    P = P & "(.*)"                            '1. The beginning'
    P = P & "(create trigger)"                '2. Need to change 'CREATE' to 'ALTER''
    P = P & "(.*$)"                           '3. Rest of the first line'
    P = P & "(^\s*declare\s*@is_mergeagent)"  '4. First declaration line'
    P = P & "([\s\S]*)"                       '5. The middle part'
    P = P & "(if\s*@@error[\s\S]*)"           '6. The lines after ...'
    P = P & "(FAILURE:[\s\S]*)"               '7. ... where we add our workaround'

    ModifyTrigger = RegExReplace(P, TriggerSQL, _
                                 "$1ALTER trigger$3" & vbCrLf & _
                                 DeclarationSection & vbCrLf & _
                                 "$4$5" & vbCrLf & _
                                 ExecuteSection & vbCrLf & vbCrLf & _
                                 "$6$7", , True, True)
End Function

Private Function RegExReplace(SearchPattern As String, TextToSearch As String, ReplacePattern As String, _
                      Optional GlobalReplace As Boolean = True, _
                      Optional IgnoreCase As Boolean = False, _
                      Optional MultiLine As Boolean = False) As String
Dim RE As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = MultiLine
        .Global = GlobalReplace
        .IgnoreCase = IgnoreCase
        .Pattern = SearchPattern
    End With

    RegExReplace = RE.Replace(TextToSearch, ReplacePattern)
End Function


'Execute pass-through SQL'
Private Sub ExecPT(SQL As String, Optional DbName As String = "MyDB")
Const QName As String = "TemporaryPassThroughQuery"
Dim qdef As DAO.QueryDef

    On Error Resume Next
    CurrentDB.QueryDefs.Delete QName
    On Error GoTo 0
    Set qdef = CurrentDB.CreateQueryDef(QName)
    qdef.Connect = "ODBC;Driver={SQL Server};Server=myserver;database=" & DbName & ";Trusted_Connection=Yes;"
    qdef.SQL = SQL
    qdef.ReturnsRecords = False
    CurrentDB.QueryDefs(QName).Execute

End Sub