服务器端仅前进游标中断@@ IDENTITY [英] Server-side forward-only cursor breaks @@IDENTITY

问题描述

这是一个最小的复制示例.

Here is a minimal repro example.

数据库:

CREATE TABLE temp (x int IDENTITY(1, 1), y int);

代码(使用VBA和ADO):

Public Sub repro()
    Dim cn As New Connection
    Dim rs1 As New Recordset
    Dim cmd As New Command
    Dim rs2 As New Recordset

    cn.Open "Provider=SQLNCLI11;Server=myServer;Database=myDatabase;Trusted_Connection=Yes"

    rs1.Open "SELECT 1", cn, adOpenForwardOnly      ' [X] '

    cmd.ActiveConnection = cn
    cmd.CommandText = "INSERT INTO temp (y) VALUES (1) "
    cmd.Execute

    rs2.Open "SELECT @@IDENTITY", cn, adOpenStatic
    Debug.Print rs2(0).value

    rs2.Close
    rs1.Close                                       ' [X] '
    cn.Close
End Sub

预期结果: Debug.Print行将一个整数输出到调试窗口.

Expected result: The Debug.Print line outputs an integer to the debug window.

实际结果: Debug.Print行将Null输出到调试窗口.

Actual result: The Debug.Print line outputs Null to the debug window.

注释:

• 删除标记为[X]的行后，代码将按预期工作(最后插入的标识值将写入调试窗口).
• 这是一个最小的复制示例:我知道服务器端游标是邪恶的".我知道，在这种特殊情况下，将SELECT SCOPE_IDENTITY()添加到INSERT批处理是获取新插入的ID的正确方法.这只是用最少的代码重现该问题的一个最小示例.修改旧版代码时，我偶然发现了这个问题.
• 已使用SQL Server Native Client 11.0和经典" MDAC SQL Server ODBC驱动程序进行了测试.在SQL Server 2005和2012上进行了测试.没有什么区别.

• As soon as I remove the lines marked as [X], the code works as expected (the last inserted identity value is written to the debug window).
• This is a minimal repro example: I know that server-side cursors are "evil". I know that in this particular case adding SELECT SCOPE_IDENTITY() to the INSERT batch is the correct way to get the newly inserted ID. This is just a minimal example to reproduce the issue with the least code possible. I stumbled upon this issue while modifying legacy code.
• Tested with both SQL Server Native Client 11.0 and the "classic" MDAC SQL Server ODBC driver. Tested with SQL Server 2005 and 2012. Doesn't make a difference.

我的问题:这是设计使然，还是我偶然发现了SQL Server错误?如果是前者，它在哪里记录?

将两个选项(带有和不带有[X])与SQL Server Profiler跟踪进行比较，有一个显着的区别:当包含[X]行时，显然会删除并重新打开连接(Audit Logout -Audit Login)在cmd.Execute和rs2.Open之间.我想这解释了为什么@@IDENTITY不再起作用.

Comparing the two options (with and without [X]) with an SQL Server Profiler trace, there is one significant difference: When the [X] lines are included, the connection is apparently dropped and reopened (Audit Logout - Audit Login) between cmd.Execute and rs2.Open. I guess that explains why @@IDENTITY no longer works.

留下了以下问题:为什么在一种情况下ADO(或SQLNCLI11驱动程序?)关闭并重新打开连接，而在另一种情况下却没有关闭?在哪里记录?

That leaves the following question: Why does ADO (or the SQLNCLI11 driver?) close and reopen the connection in one case but not in the other case? And where is this documented?

推荐答案

MARS是默认行为的一种更好的选择，它实际上允许多个记录集.

MARS is a nicer alternative to the default behavior which does actually allow multiple recordsets.

会发生什么:

1. SELECT 1充当连接&的活动记录集.保持开放
2. 然后执行insert时，提供程序知道它具有活动记录集，并尝试通过创建新的连接来执行该语句而不干扰任何内容来提供帮助
3. 此临时连接执行insert，然后通过执行注销来清除-破坏与之关联的状态
4. select @@identity再次使用临时连接，其中前一个语句的@@identity超出了范围，因此NULL.

1. SELECT 1 acts as the active recordset for the connection & remains open
2. When you then execute the insert the provider knows it has an active recordset and tries to be helpful by creating a new connection to execute the statement without interfering with anything
3. This ephemeral connection executes the insert then tidies up by perfoming a logout - destroying state associated with it
4. select @@identity again uses an ephemeral connection where @@identity for the previous statement is out of scope, hence NULL.

这篇关于服务器端仅前进游标中断@@ IDENTITY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！