identity_insert in SQL 2005

Hi,

we're currently doing test runs to make our application run on SQL 2005. In one of our projects, we need to replicate data from one master database to several slave databases. As the primary keys need to be the same, I used the "set identity_insert <table name> on" command to insert the exact same keys in the respective identity columns. This has always worked on our SQL 2000 databases. However, in the test runs on SQL 2005, I get the following error message:

"Cannot insert explicit value for identity column in table 'NomenclatuurSleutel' when IDENTITY_INSERT is set to OFF."

I cannot understand why I get this error message. Our components run in COM+, I execute the "set identity_insert on" statement right before the insert statement, and switch it off again right away. This all happens in the same method, and thus I expect it to be executed in the same transaction. A trace on SQL server confirms this: the statements all have the same transaction ID. However, there are 2 lines that are worrying me a bit: between the "set identity_insert" and the insert statement, there seems to be a rollback-action... but this has a different transaction ID, so I don't know whether this affects the identity-insert statement...

Here's a screenshot of the trace:

trace

I am clueless as to where I should look next, is there a difference in transaction management between SQL 2000 and 2005, is the identity_insert statement executed differently,... ?

Looks like you are using connection pooling...notice the RPC "sp_reset_connection" call in between your statements? That is used by data providers (i.e. OleDB, Sql Client, etc.) to reset a connection when reused from a pool...One of the many things that does is abort any open transactions...

Take a look here for some additional information:

http://www.sqldev.net/misc/sp_reset_connection.htm

I'm only guessing that the trace was performed on a single SPID, if so this is your problem, as the sp_reset_connection will abort your transaction amoung other things...

HTH,

|||

I was afraid that would be the case, as I came across that very page you're linking to. I do find it strange, however, that this has always worked on our SQL 2000 databases. And the sp_reset_connection stored procedure was executed as well, no problems there though.

I did find the following comment on a blog somewhere:

When a connection gets pulled out of the pool, an "exec sp_reset_connection" quitely gets sent, and this resets the connection state. With SQL 2000, a few things (like the isolation level) don't get reset, but most things, including the current database, do. With SQL 2005, everything is supposed to get reset.

Apparently, the few things that didn't get reset, are the things that kept my code running.

I tested some new code to work around this, by sending the three SQL-statements (identity_insert on, insert-statement and identity_insert off) in one batch to the server instead of three separate commands. But I'm a little reluctant to go ahead with this, as I'd need to change a lot of code. I'd much rather see a solution in the form of a configuration adjustment in SQL Server, could this be possible?