Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kirti_girdhar
Starting Member
2 Posts |
 Posted - 2007-05-03 : 04:07:00
|
| 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 INSERT2. Calculates the auto increment field ID using SELECT @@IDENTITY3. 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 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-03 : 05:19:33
|
| use scope_identity()_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
|
|
|
how to change the sql queries generated internally