@@IDENTITY returning NULL if an open connection already exists.

[cawthor]

I don't know if this should be in the SQL forum or here, but here goes.

I have a development and production instance of my code and this works fine on dev (of course!). Both servers have the same setup (using SQL Server 2000). In my code, I am inserting a record into a table and retrieving the @@IDENTITY value. However, the prod code is returning NULL. I have traced it down to the fact that I already have an open recordset on the table I am inserting into. If I close that recordset prior to my insert, then the identity value is returned successfully. Does anyone know if there is some setting in the DB or table level somewhere that is causing me to lock it with the open recordset??

Any help would be appreciated!

 

[DotNetGnat]

how about using...scope_identity() instead of @@identity

something like this...

SELECT scope_identity() as myid

does this work...

-DNG

 

[cawthor]

Scope_Identity does not work, but I just found IDENT_CURRENT('table_name') and that seems to work. Something to do with this working across all sessions but I still dopn't know why this works on one server and not the other. I guess this is the solution for me but I'd still lke to understand why!

 

[DotNetGnat]

but did you read that ident_current() is not good for larger tables...

-DNG