SCOPE_IDENTITY with ASP

[Larry]

I originally asked this on comp.databases.ms-sqlserver, but they've
referred me here.

I am seeing a problem with an ASP application, where I have 2 tables.
In the first table, the ASP inserts just 1 row and retrieves the
primary key of the new row using SCOPE_IDENTITY. It then uses that
primary key in the column of a second table (foreign key) to insert
many rows.

What I'm seeing is an intermittent problem where the foreign key in the
second table is not what it should be. I think the problem may be due
to the fact that the insert into the first table and the calling of
SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
in between.

Is it possible that 2 users may be calling my ASP page at the same time
and causing a concurrency problem due to the INSERT and the
SCOPE_IDENTITY being done in 2 different SQL statements? I read that
SCOPE_IDENTITY always returns the last identity value generated from
"the current connection", so I thought that would mean that it wouldn't
get messed up by another ASP request. But now I'm thinking that
perhaps ASP uses connection pooling which could mean that 2 users could
be sharing the same connection which would cause this concurrency
issue.

Does anyone know if my theory of what's wrong is plausible?

[Adam Knight]

You haven't posted any code, so it is hard to make any
informed comments.

If you are not encapsulating your SQL logic in stored
procedure, consider doing so that way you have a degree
of separation amongst tasks.

Take a look at this link and see if it opens anything up
for you!

http://www.aspfaq.com/show.asp?id=2174

[Bob Barrows [MVP]]

Larry wrote:

I originally asked this on comp.databases.ms-sqlserver, but they've
referred me here.

I am seeing a problem with an ASP application, where I have 2 tables.
In the first table, the ASP inserts just 1 row and retrieves the
primary key of the new row using SCOPE_IDENTITY. It then uses that
primary key in the column of a second table (foreign key) to insert
many rows.

What I'm seeing is an intermittent problem where the foreign key in
the second table is not what it should be. I think the problem may
be due to the fact that the insert into the first table and the
calling of SCOPE_IDENTITY are done in 2 separate ASP statements with
some ASP code in between.

Is it possible that 2 users may be calling my ASP page at the same
time and causing a concurrency problem due to the INSERT and the
SCOPE_IDENTITY being done in 2 different SQL statements? I read that
SCOPE_IDENTITY always returns the last identity value generated from
"the current connection", so I thought that would mean that it
wouldn't get messed up by another ASP request. But now I'm thinking
that perhaps ASP uses connection pooling which could mean that 2
users could be sharing the same connection which would cause this
concurrency issue.

Does anyone know if my theory of what's wrong is plausible?

As Adam said, we can't answer without seeing the code. My suggestion (which
again echoes Adam) would be to encapsulate the inserts into a single stored
procedure.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"