Why isn't SCOPE_IDENTITY() returning my inserted ID?

Question

I have an insert statement executed via ADODB.Connection.Execute inserting into a table on MSSQL server with an identity column, but the returned recordset does not return the ID of the newly inserted record.

I have tried the following forms of obtaining the inserted ID of the record.

INSERT INTO blah VALUES (blah); SELECT SCOPE_IDENTITY()
INSERT INTO blah VALUES (blah); SELECT @@IDENTITY
INSERT INTO blah OUTPUT INSERTED.ID INTO @ID VALUES(blah); SELECT ID FROM @ID

In all cases, when querying the recordset returned it does not contain the identity

var I = DB.Execute(insert_statement_from_above);
if (I) {
  var INSERTED_ID = I(0).Value;
}

Why might this be happening?

Answer 1

Check the table being inserted into for any INSERT triggers, and check that those INSERT triggers are using SET NOCOUNT ON.

When a trigger does not specify SET NOCOUNT ON, if it updates the database, it will generate a result set, that result set will appear before the result set that contains the SCOPE_IDENTITY result.

See: https://msdn.microsoft.com/en-us/library/ms189837.aspx

In reality what is happening is that the recordset contains one or more result sets.

TRIGGER RESULT SET
SCOPE_IDENTITY RESULT SET

When you query the recordset for a scope identity you are actually querying the result set output by the trigger.

You can use RS.nextRecordset() and then query the SCOPE_IDENTITY but if the trigger may or may not perform an update you end up not knowing which result set contains your SCOPE_IDENTITY, is it the first or the second, is there a second?

It is generally accepted good practice for triggers to specify SET NOCOUNT ON however if you absolutely must have SET NOCOUNT OFF in your trigger or have no control over the trigger, you can work around it with the following code.

Note: The Scope Identity in the examples above will always be the last result set in the recordset. So we can find it as follows:

var I = DB.Execute(insert_statement_from_above);
if (I) {
  // find the result set with the scope identity in it
  while (I.State == 0) {
    I = I.nextRecordset();
  }
  var INSERTED_ID = I(0).Value;
}

This looks for the result set that contains your scope identity by ignoring the earlier closed result sets.

See Also:

State property, nextRecordset()