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

[cawthor]

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

I have a development and production instance of my ASP 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]

this recent thread thread183-1112931 might give you more insight

-DNG

 

[cawthor]

Thanks.

IDENT_CURRENT('table_name') seems to work. Something to do with this working across all sessions but I still don'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!