How To Change The Sql Queries Generated Internally

We are trying a cluster setup on MS SQL 2005 with one m/c as Publisher (Primary) and another as Subscriber (Secondary).

When Publisher and Subscriber both are running, everything goes fine but when Publisher server goes down, subscriber server troubles.



We are using MSADO15.DLL for database connectivity.

We have a table where ID column is set to primary key with Auto Increment true.

We use AddNew() function of this library to insert a new record.

We fill a structure with all necessary values to pass it to AddNew() with ID field set to 0.

When we use AddNew() on the above table to insert a record, the ID auto inserted in table is correct but returned structure contains wrong ID value.



We tried to trace this problem using SQL Server Profiler Tool of SQL Server.

AddNew() function performs following operations in back end

1. Inserts the record to the table using INSERT

2. Calculates the auto increment field ID using SELECT @@IDENTITY

3. Fills this ID in the structure passed to AddNew() and returns it.

But ID returned by SELECT @@IDENTITY query is wrong.

There are other ways also to retrieve the last ID inserted in table by

IDENT_CURRENT(‘table_name’) which return right ID.



Can we change the call of SELECT @@IDENTITY to IDENT_CURRENT in AddNew() functions behavior of MSADO DLL?

Or there is another way of retrieving right ID