3

Here is a minimal repro example.

Database:

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

Code (using VBA and 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

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

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

Notes:

  • 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.

My question: Is this behavior by design, or did I stumble upon an SQL Server bug? If the former, where is it documented?

EDIT: 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.

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?

Heinzi
  • 167,459
  • 57
  • 363
  • 519
  • It's possibly neither. Cursors require a bunch of invisible setup (you can see part of it if you enable profiling). As part of that invisible setup, I can vividly imagine `@@IDENTITY` getting borked by virtue of the first rowset cursor still being active and requiring some operation under the covers. Try hooking up a profiler and see if there's anything of that nature getting sent over the wire. – Jeroen Mostert Jan 29 '18 at 11:04
  • @JeroenMostert: Great idea. I did a profiler trace and added the result to my question. – Heinzi Jan 29 '18 at 12:55
  • ADO closing and opening the connection also explains how this code can work in the first place -- because, without MARS, only one active result set is possible on a connection. Your code, if it worked literally as written, would require two. Reading from `rs1` would/should fail if `rs2` is open, and closing `rs1` before trying to open `rs2` should have the same effect as eliminating `rs1`. Try checking `rs1.State`; I expect it to be closed implicitly due to `rs2` opening. This may be an ADO failsafe since poor cleanup code is common; that it messes up your identity is unintentional. – Jeroen Mostert Jan 29 '18 at 13:08
  • Note that if that *is* your problem, `SCOPE_IDENTITY()` will not fix anything either -- the only thing that would is explicitly creating a new connection for `rs2` so there's no interference from another, possibly open recordset on `cn`. (What may also work, but I'm not sure about that, is incorporating an output parameter in the command and using something like `INSERT ... ; SELECT @outparm = SCOPE_IDENTITY()`. This avoids a rowset, but does send something back while a rowset is active, and I'm not sure that's legal.) – Jeroen Mostert Jan 29 '18 at 13:32
  • @JeroenMostert: Makes sense, thanks for the analysis. Interestingly, `rs1.State` is (officially) still `adStateOpen` right until `rs1.Close`. About your last comment: Opening a recordset with the command `INSERT ...; SELECT SCOPE_IDENTITY()` works fine (but needs [rs2.NextRecordset](https://stackoverflow.com/q/1201089/87698)). – Heinzi Jan 29 '18 at 13:38
  • @JeroenMostert: That's one of the two things we will change in our legacy code to avoid this problem in the future (it also protects the code from trigger identity interference). The other will be to modify our DAL to use client-side static recordsets (`rs1.CursorLocation = adUseClient`) instead of server-side cursors, which also avoids this problem. – Heinzi Jan 29 '18 at 13:40
  • Hmm, maybe I'm off base but all legacy ADO code I've written requires the table name defined in the SQL. Maybe that's the culprit? E.g. it should be something like: `rs1.Open "SELECT 1 FROM SomeTable", cn, adOpenForwardOnly` – Ryan Wildry Jan 29 '18 at 14:31
  • @RyanWildry: Nope, that's not it. Originally, it *was* `"SELECT x FROM SomeTable"`, but I simplified it for the example so that people could reproduce it without adding yet another table. – Heinzi Jan 29 '18 at 14:47
  • Ahh, makes sense. – Ryan Wildry Jan 29 '18 at 15:32

2 Answers2

2

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

What happens is:

  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.

enter image description here

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Wow, that's much more insidious than the behavior I guessed -- there's not even a guarantee it will use the same connection object you passed, it'll just happily create a new one without telling you. On the one hand, I feel bad for not reproducing it instead of guessing. On the other hand, I feel good for no longer having a ready setup to reproduce ADO-related problems... – Jeroen Mostert Jan 29 '18 at 15:04
  • Nice catch! I *did* profile it, but the idea that ADO.Connection would silently open a new connection never crossed my mind, so I did not pay attention to the SPID column... – Heinzi Jan 29 '18 at 15:49
0

I noticed you are doing two selects on the same connection. Have you tried enabling "Multiple Active Result Sets" by adding "MultipleActiveResultSets=True" to your connection string?

Enabling Multiple Active Result Sets on MSDN

sarin
  • 5,227
  • 3
  • 34
  • 63
  • This is ADO, not ADO.NET. I still tried it: `MultipleActiveResultSets=True` does not make a difference. – Heinzi Jan 29 '18 at 11:01