Posted by manstein on 11/29/05 18:33

like I said earlier, if you know the name of the table where you are
inserting the file details, then the surest way to query the identity
value is with IDENT_CURRENT('<table name>'). The problem with
@@Identity var is that it is global to the session (i.e. connection)
which is why it is affected by triggers. Also, if the insert happens
in a rolled back transaction, the @@identity is NOT reset so it is
possible to have an invalid @@identity value. This may or may not be a
problem for you. Now, the problem with the scope_identity() is this
returns the IDENTITY value that is local to the scope of the insert.
This means you would have to query the IDENTITY value of the last
insert in the same code that the insert occurred (same session, same
local code scope(i.e. job)). I myself use scope_identity() but for
you, you may not be looking to query the IDENTITY in the same session.
For instance, your inserts might happen in one connection to the db and
you might instantiate a separate connection and query the IDENTITY
column. In that case, scope_identity() returns NULL. You would know
better these details so please post code or more info if this doesn't
help. Thanks.

[Back to original message]