ADO 2.8 recordset AddNew / Update - what determines whether @@Identity or Scope_Identity() is used

Does anybody know what determines whether @@Identity or Scope_Identity() is used in the auto generated SQL sent to SQL Server by the ADO recordset AddNew/ Update methods in classic ADO 2.8 with Client side cursor / Lock Optimistic, and with SQL Native Client 10 in the connection string.

The problem I have is that on my own development computer, I can see, using Profiler,  that SELECT SCOPE_IDENTITY() AS SCOPE_ID_COLUMN is part of the SQL generated by the Update - which is exacty what I want.

Yet, the same compiled program on a customer's server generates SELECT @@IDENTITY - which is bad news on a replicated database, as this means that the wrong ID is sometimes returned to the recordset - due to the insert triggers used in SQL 2008 replication.

I've compared loaded DLL versions, assuming it must be due to the wrong/old dependency file related to data access, and I can't see anything abnormal here.

What is going on ?  How can I make the Recordet Update method always use Scope_Identity() to retrieve the  identity for the new record.
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New Connection
cn.ConnectionString = "Provider=SQLNCLI10.1; Data Source=MARSBAR; Initial Catalog=GMach; Integrated Security=SSPI"
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
rs.CursorLocation = adUseClient
rs.Open "SELECT TOP 1 * FROM Contacts ORDER BY ID DESC", cn, adOpenStatic, adLockOptimistic
rs!Name = "Testing"

Open in new window

Who is Participating?
MTSDLConnect With a Mentor Author Commented:
Thanks for for efforts, but I finally got the answer from an MSDN forum:

Apparently this was an issue that was not fixed until Windows XP Service Pack 3, and Vista SP1.
My computer is XP SP3, the customers computer was on XP SP2 - hence the difference in behaviour.
I'm confused...  what part get's automatically generated?
Typically you'd write the event handler your self
MTSDLAuthor Commented:
The update method of an ADO recordset after an AddNew inserts the new record to the table by generating some SQL (INSERT INTO...) to execute on the SQL server, and this also includes SELECT statement which returns the new identity of the auto-increment field.  
On my development comuter SELECT Scope_Identity() is part of the SQL sent, on a customers server, it seems that SELECT @@identity is sent.
Scope_Identity() returns the correct new identity to the recordset, whereas @@identity may or may not.
The result is that the ADO recordset may or may not contain the correct identity for that new record.
Event handlers don't come into it.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Are you sure?   I've never heard of ADO automatically generating a select statement on an insert.
Back in the VB6 days, using "classic" ADO, we had to write our code for this...   Are you using some sort of home-made helper class?   Are you talking about a stored procedure that runs on an SQL Server to perform an Insert followed by a Select?
MTSDLAuthor Commented:
Absolutely sure - I've seen it with my own eyes in SQL Profiler, and this is the way the new identity is returned if there is one when using AddNew/Update.  It's all under the bonnet ADO magic, and barely documented, maybe because it is provider specific.
Anyway I may have found an answer in the following:
Though this would only make sense if have this fix already installed on my development pc - and I don't  think I've knowingly done that.
Will check later.
OK... I'm finally following you... yes, the Jet provider does automatically return the last autonumber on an Update in order to fill in the value for the autonumber field in the RecordsSet
So, why not just ignore the automatically created stuff and write your own... that way you'd be able to choose the right version.   For example, use the follow after each insert:

   Set rs = con.Execute("SELECT @@Identity")
   ID = rs.Fields.Item(0).Value

Open in new window

grayeConnect With a Mentor Commented:
Hang on... Are you saying that the RecordSet is already returning the wrong ID (or possibly the wrong ID)?   If so, then you're probably not all that interested in an alternate way to get to the Autonumber, since it's the RecordSet's values that you're trying to fix.
Whew!....  I *FINALLY* figured out what this question is all about!   No, I don't know how to influence the internal workings of the ADO provider.   But let me see if I can help (since I haven't been of much help so far)
  • Are the "SQL Compatibility" levels for the two databases the same... (both set to 90 or 100)?
  • I presume you've checked the connection strings
  • Same OS on both systems (not Vista vs XP)?
  • Same SQL Versions (including Service Packs, Hotfixes, etc)?
  • Same "bit-ness" 32-bit vs 64-bit?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.