"Executing SQL directly; no cursor" error when using SCOPE_IDENTITY/IDENT_CURRENT

Question

There wasn't much on google about this error, so I'm asking here. I'm switching a PHP web application from using MySQL to SQL Server 2008 (using ODBC, not php_mssql). Running queries or anything else isn't a problem, but when I try to do scope_identity (or any similar functions), I get the error "Executing SQL directly; no cursor". I'm doing this immediately after an insert, so it should still be in scope. Running the same insert statement then query for the insert ID works fine in SQL Server Management Studio. Here's my code right now (everything else in the database wrapper class works fine for other queries, so I'll assume it isn't relevant right now):

function insert_id(){
    return $this->query_first("SELECT SCOPE_IDENTITY() as insert_id");
}

query_first being a function that returns the first result from the first field of a query (basically the equivalent of execute_scalar() on .net).

The full error message: Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor., SQL state 01000 in SQLExecDirect in C:[...]\Database_MSSQL.php on line 110

Answer 1

you could try using the OUTPUT clause as a work around:

INSERT INTO YourTable
        (col1, col2, col3)
        OUTPUT INSERTED.YourIdentityCol as insert_id
    VALUES (val1, val2, val3)

this single statement will insert the row and return a result set of the identity values.

working sample:

create table YourTestTable (RowID int identity(1,1), RowValue varchar(10))
go

INSERT INTO YourTestTable
        (RowValue)
        OUTPUT INSERTED.RowID as insert_id
    VALUES
        ('abcd')

OUTPUT:

insert_id
-----------
1

(1 row(s) affected)

this is also good if you insert multiple rows at one time:

INSERT INTO YourTestTable
        (RowValue)
        OUTPUT INSERTED.RowID as insert_id
    SELECT 'abcd'
    UNION SELECT '1234'
    UNION SELECT 'xyz'

OUTPUT:

insert_id
-----------
2
3
4

(3 row(s) affected)

Answer 2

I'm not sure how you executing the actual insert statement but scope_identity() will only return the last identity value for that session i.e the same SPID.

If you connect to the DB and perform the insert and then connect to the DB again scope_identity() will always return NULL as they are in two different sessions.