T-SQL - SELECT SCOPE_IDENTITY does not work in vba code

Question

I am working on a project which consists transferring a few thousands of Excel rows to a SQL Server (it was also called T-SQL if I'm right?) database. I put together some logic in VBA to shape up the data.

Let me give you some context first about this. The data I'm about to transfer are invoice files. On each row, there are code of the stock items, prices, invoice number, invoice date, name of the client etc. These needs to be transferred to the database of a proprietary ERP system.

There are two tables on the database which I'm interested in for now. First one holds the header data for the invoice (client data, date, invoice number, invoice total etc.). Second table holds the information on the stock items (what has been sold, how many and for how much money etc).

After each insert onto the first table, I have to get the inserted row's primary key, in order to insert rows to the second table, which requires the PK of the first table on each row.

Now, my approach was to use the SCOPE_IDENTITY() function of the T-SQL. When I try to do it on the database directly via SQL Server Management Studio, it works without a hitch.

But when I try to use it in the code, it returns an empty recordset.

Code I'm using is as follows:

Public Function Execute(query As String, Optional is_batch As Boolean = False) As ADODB.Recordset

    If conn.State = 0 Then
        OpenConnection
    End If

    Set rs = conn.Execute(query) 'this is the actual query to be executed

    Dim identity As ADODB.Recordset 'this rs supposed to hold the PK of inserted row, but returns an empty recordset

    Set identity = conn.Execute("SELECT SCOPE_IDENTITY();")

    If TypeName(identity.Fields(0).Value) = "Null" Then
        pInsertedId = -1
    Else
        pInsertedId = identity.Fields(0).Value 'I'm saving it in an object variable, to access it easily later on
    End If

    Set Execute = rs 'to be returned to the caller

    'closing the connection is handled outside this procedure

End Function

When I run this on VBA, second query SELECT SCOPE_IDENTITY();just returns an empty recordset. Same query works successfully when ran on the db directly.

Actually I'm able to pull this off by other means. There is a UUID column which I'm supposed to insert to the row in the first table. I can just simply query the table with this UUID and get the PK, but I'm just curious why this won't work.

Any ideas?

Your `scope_identity` is the [only statement in the batch](https://stackoverflow.com/q/3526800/11683) (which is the scope). You need to [put it into the recordset query batch](https://stackoverflow.com/q/1201089/11683). — GSerg, Feb 23 '19 at 08:30
**SQL Server** is the relational database **product** offered by Microsoft - while **T-SQL** (Transact-SQL) is the "dialect" of the SQL (query) language used by SQL Server (any Sybase) — marc_s, Feb 23 '19 at 08:37

score 2 · Accepted Answer · answered Feb 23 '19 at 08:31

Your code doesn't insert any data, so no identity values are generated in the current scope, as defined in the official documentation for SCOPE_IDENTITY():

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

Your code effectively is the same as inserting data in one query window in SSMS and querying SCOPE_IDENTITY() in another query window. Well, this isn't how it works. You must query it in the same scope, i.e. a stored procedure, trigger, function, or batch. Otherwise, use ID values generated by you and insert them with the data.

Ok, now it figures. I remember reading somewhere that as long as queries are in the same connection, SCOPE_IDENTITY() should work, but obviously not. Thanks a lot. — SercioSoydanov, Feb 23 '19 at 08:42

T-SQL - SELECT SCOPE_IDENTITY does not work in vba code

1 Answers1