asked on

Insert into table and ScopeIdentity sometimes failing

I'm a user of a solution based on an MS SQL 2005 database.

A stored procedure is regularly called which will execute the following:
INSERT INTO table WITH(ROWLOCK) DEFAULT VALUES
SET @id = Scope_Identity()

Most times this works fine, but every now and again a scope identity value is returned, but the row is not there. No errors are seen, by all accounts the sproc looks like it's been successful.

I'd be interested to know if anyone has experienced this kind of problem, if it's related to parallelism and if there is any decent cure?

Guy Hengel [angelIII / a3]

the WITH ROWLOCK should not be needed...

now, unless there is a explicit BEGIN TRANSACTION and a "missing" COMMIT, a explicit ROLLBACK or an error making the transaction to rollback, this is either not possible, or a bug.

what is the exact version you have of sql server?

BrandonGalderisi

Perhaps you have a trigger on the table with a soft failure. scope_identity() will be populated still.

use tempdb
go
create table asdfasdfasdfasdfasdfasdf (i int identity,b char(1))
go
create trigger a_Check on asdfasdfasdfasdfasdfasdf
for insert
as
begin
delete from a
from asdfasdfasdfasdfasdfasdf a
join inserted i
on a.i=i.i
end
go
select scope_identity()
insert into asdfasdfasdfasdfasdfasdf(b) values('a')
select scope_identity()
go
drop trigger a_check
go
drop table asdfasdfasdfasdfasdfasdf

Open in new window

BrandonGalderisi

scope_identity() returns the last identity value for the connection, not the last statement.

See this example. scope_identity() is still 1 after the second insert fails due to an error.

use tempdb
go
create table asdfasdfasdfasdfasdfasdf (i int identity,b char(1))
go
select scope_identity()
insert into asdfasdfasdfasdfasdfasdf(b) values('a')
select scope_identity()
go
insert into asdfasdfasdfasdfasdfasdf(b) values('ab')
select scope_identity()
 
go
select * from asdfasdfasdfasdfasdfasdf
go
drop table asdfasdfasdfasdfasdfasdf

Open in new window

catherinelouise

ASKER

No triggers on this table, no begin transaction either.

We are using Microsoft SQL Server 2005 - 9.00.3282.00 (X64) Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

catherinelouise

ASKER

Thanks for your quick responses btw guys.

So I see what you're saying BrandonGalderisi - but say the insert HAS failed, and i've got a scope identity from some other transaction, I would still expect that scope identity to be a valid value of something, right?

I find that the scope identity value doesn't relate to anything - although it is sequentially correct for that table. If I select from the table and order by the PrimaryKey (aka the @ID value) - I will see it is sequential but every now and then it skips by one. Which seems odd to me.

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

catherinelouise

ASKER

Ah - now that is interesting then...

Can you give me any advice on if/how/where I can capture an insert taking place within a stored procedure failing?

BrandonGalderisi

Well generally you would be checking the status of @@error or using try/catch logic to do error trapping.

catherinelouise

ASKER

Okey dokey, I have a path forward, I'll see if I can capture the insert failing. Thanks both.