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

Posted on 2009-02-17
Last Modified: 2012-05-06
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

Question by:MTSDL
    LVL 41

    Expert Comment

    I'm confused...  what part get's automatically generated?
    Typically you'd write the event handler your self

    Author Comment

    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.
    LVL 41

    Expert Comment

    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?

    Author Comment

    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.
    LVL 41

    Expert Comment

    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

    LVL 41

    Assisted Solution

    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?

    Accepted Solution

    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.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now