Hi, Dmitry,

I don't immediately see an issue - you're sure that an INSERT was done on that ocnnection, just prior to you calling scope_identity?

This may not answer your specific question, but here's a thought - I noticed you posted this under SQL2005. In 2005, there's an alternative to using scope_identity that (in my opinion) is a bit more efficient. SQL2005 now includes an OUTPUT clause that allows you to get immediate feedback on what's been inserted (or updated). So you could do something like this...

DECLARE @tTestTable TABLE ( MainPK  [int] IDENTITY(1,1) NOT NULL,  Name Char(50)) 
INSERT @tTestTable OUTPUT Inserted.MainPK  VALUES ('Kevin Goff')     

That will return the value of the identity columnn that was just inserted.

(Output basically provides visibility to the INSERTED and DELETED system tables, that previously were only visibile in a database trigger)


If you have multiple inserts and want to direct them to a temporary area, you can redirect the OUTPUT...

DECLARE @tTestTable TABLE ( MainPK  [int] IDENTITY(1,1) NOT NULL,  Name Char(50)) 

declare @ttemp table (mainpk int)
INSERT @tTestTable OUTPUT Inserted.MainPK  into  @ttemp VALUES ('Kevin Goff')     
INSERT @tTestTable OUTPUT Inserted.MainPK  into  @ttemp VALUES ('Steve Goff')     

select * from @ttemp   -- will give you 1 and 2

Again, that doesn't answer your specific question, but now that I have all my clients on SQL2005, I've cut down on scope_identity in favor of this approach.

Kevin