Server-side Forward-only Cursor Breaks @@IDENTITY

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 A

Solution 1:

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.

Solution 2:

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