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"