September 9, 2002 at 12:40 pm
I have been using MS ActiveX Data objects (ADO) for awhile. Just ran into an issue: I always used a single Dbase connection for multiple recordsets (most of the time having only 1 open recordset at a time). Well, this also seemed to work fine with nested ASP code (server-side Javascript on a web page) that created multiple recordsets for reading off of a single connection. However, aftering opening a recordset and then trying to do an INSERT followed by a SELECT @@IDENTITY to get the key for a foreign key insert (in subsequent ADO calls), the ident value was NULL. It took me the longest time to figure out that because I was looping through another recordset on the same connection, the IDENTITY value was not valid. Furthermore, when I embedded the SELECT @@IDENTITY right into the same SQL batch statement, the identity value returned correctly, but SQL Server 2000 would then insert multiple duplicate rows (instead of just one) with different ident values!!! WEIRD!
Anyway, now I use only one open connection for each simultaneously open recordset and have had no problems.
I do not know if this is an issue with ADO.NET. The cursor type or lock type had no impact on the behavior described above. Using DataConnection.Execute rather than a recordset/cursor also had no corrective impact.
September 9, 2002 at 2:30 pm
The use of @@IDENTITY has issues which go beyond what you've described here. @@IDENTITY will return back the last identity value inserted by that connection, whether the user did it or something like a trigger did. For instance, you have a trigger for INSERT on table MyTable. The trigger inserts a record into MyAuditTable every time someone inserts into MyTable. If MyTable and MyAuditTable both have identity columns, SELECT @@IDENTITY is going to return the identity based on the insert into MyAuditTable.
In SQL Server 2000 there are two options unavailable in earlier versions: SCOPE_IDENTITY() and IDENT_CURRENT().
SCOPE_IDENTITY() returns the last IDENTITY value based on the current scope. So in the case of the trigger firing, SCOPE_IDENTITY will not pick up the @@IDENTITY from its insert, but rather from the original insert into MyTable.
IDENT_CURRENT returns the last IDENTITY value for a given table. This is regardless of scope and regardless of connection. So if MyTable's last IDENTITY value was 57, and you ran SELECT IDENT_CURRENT('MyTable'), you'd get back 57, even if you weren't the one to insert the record which pushed it up to 57.
HTH.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply