|
Home > Archive > IIS ASP > April 2005 > SCOPE_IDENTITY with ASP
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SCOPE_IDENTITY with ASP
|
|
|
| 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?
| |
| Bob Barrows [MVP] 2005-04-29, 8:05 am |
| 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"
|
|
|
|
|