possible Scope_Identity() problem

I have an ASP.NET application with a SQL Server 2000 backend, where two
pages fire off two different stored procedures. Each stored procedure
creates a new record in a particular table then uses Scope_Identity() to get
the id of the newly created record for adding it to a link table. The
problem I'm seeing appears to be Scope_Identity() behaving as thought it
were @.@.IDENTITY, i.e. on the occasion when both procedures are fired at once
(different machines and browsers, not that it should matter), one procedure
appears to get the id of the record created by the other and essentially
steal its record.
Has anyone come across anything like this before?
thanks in advance,
--
jo inferisHi
Do you have code to reproduce this issue?
I only way I could think this happens is if both SP's are executed on the
same connection (which is unlikely).
Regards
Mike
"Jo Inferis" wrote:

> I have an ASP.NET application with a SQL Server 2000 backend, where two
> pages fire off two different stored procedures. Each stored procedure
> creates a new record in a particular table then uses Scope_Identity() to g
et
> the id of the newly created record for adding it to a link table. The
> problem I'm seeing appears to be Scope_Identity() behaving as thought it
> were @.@.IDENTITY, i.e. on the occasion when both procedures are fired at on
ce
> (different machines and browsers, not that it should matter), one procedur
e
> appears to get the id of the record created by the other and essentially
> steal its record.
> Has anyone come across anything like this before?
> thanks in advance,
> --
> jo inferis
>
>|||Hi Jo,
Use IDENT_CURRENT('table_name') to get the Identity value. Because
IDENT_CURRENT returns the last identity value generated for a specific table
in any session and any scope.
@.@.IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in
the current session and the current scope.
Regards
Sivakumar
"Jo Inferis" wrote:

> I have an ASP.NET application with a SQL Server 2000 backend, where two
> pages fire off two different stored procedures. Each stored procedure
> creates a new record in a particular table then uses Scope_Identity() to g
et
> the id of the newly created record for adding it to a link table. The
> problem I'm seeing appears to be Scope_Identity() behaving as thought it
> were @.@.IDENTITY, i.e. on the occasion when both procedures are fired at on
ce
> (different machines and browsers, not that it should matter), one procedur
e
> appears to get the id of the record created by the other and essentially
> steal its record.
> Has anyone come across anything like this before?
> thanks in advance,
> --
> jo inferis
>
>|||Subramaniam Sivakumar wrote:
> Use IDENT_CURRENT('table_name') to get the Identity value.
That's not going to help, both identities are created in the same table.
Obviously I didn't make that clear enough.
jo inferis|||Mike Epprecht (SQL MVP) wrote:
> Do you have code to reproduce this issue?
It's a little difficult to extract the code to reproduce it, and this is
only a vague possibility anyway. I was just wondering if there might have
been something i'd missed in the usage of Scope_Identity().

> I only way I could think this happens is if both SP's are executed on
> the same connection (which is unlikely).
I did think about that, but even then, the scope in each case should be
different, shouldn't it?
jo inferis|||Is scope_identity() is returning the same identity of the other stored proc
perhaps you're application is using connection pooling. I'm guessing that
would explain how 2 different pages would end up being in the same "scope".|||I don't see how this can happen but maybe this will help:
Wrap all inserts in your stored procedure in a transaction.
We haven't seen any code so you may be doing this anyway.
"Jo Inferis" <jo@.inferis.NOSPAM.gotadsl.co.uk> wrote in message
news:%23LYkTseNFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Mike Epprecht (SQL MVP) wrote:
> It's a little difficult to extract the code to reproduce it, and this is
> only a vague possibility anyway. I was just wondering if there might have
> been something i'd missed in the usage of Scope_Identity().
>
> I did think about that, but even then, the scope in each case should be
> different, shouldn't it?
> --
> jo inferis
>