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?

Identity Range not working for master

I have set up a publisher database and a subscriber database which is a replica of the publisher. For the identity fields I set it up so that they would have a range of 10 numbers with an 80% margin. I was testing this on the subscriber and replica at t
he same time. I disconnected the subscriber, used up 8 id's, then connected and sure enough it gave me a new range. However whilst using the publisher it used up all 10 in the range and then gave me an error message! Surely it should have automatically
given me a new range once I'd hit 80% of the previous range.
Any help?
Thanks
Adrian
that depends on how large the batch is. So if you update 20 records in a
batch, it won't get updated and you blow the range.
The idea is to pick ranges that are much larger than representative batches.
So if I were you, I'd try ranges in the 1000's or set ranges that will not
be exceeded in the life time of your replication solution.
Hilary
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> I have set up a publisher database and a subscriber database which is a
replica of the publisher. For the identity fields I set it up so that they
would have a range of 10 numbers with an 80% margin. I was testing this on
the subscriber and replica at the same time. I disconnected the subscriber,
used up 8 id's, then connected and sure enough it gave me a new range.
However whilst using the publisher it used up all 10 in the range and then
gave me an error message! Surely it should have automatically given me a
new range once I'd hit 80% of the previous range.
> Any help?
> Thanks
> Adrian
|||Adrian,
as well as Hilary's reply, you could also consider manual range management
and use an algorithm that ensures no overlap in the ranges:
http://www.mssqlserver.com/replicati...h_identity.asp
HTH,
Paul Ibison
|||Hilary
I do intend to use much larger ranges, however I was just testing out the process on a smaller range to see it in action. It worked for the subscriber but not the publisher?
Any thoughts?
Regards
Adrian
"Hilary Cotter" wrote:

> that depends on how large the batch is. So if you update 20 records in a
> batch, it won't get updated and you blow the range.
> The idea is to pick ranges that are much larger than representative batches.
> So if I were you, I'd try ranges in the 1000's or set ranges that will not
> be exceeded in the life time of your replication solution.
> Hilary
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> replica of the publisher. For the identity fields I set it up so that they
> would have a range of 10 numbers with an 80% margin. I was testing this on
> the subscriber and replica at the same time. I disconnected the subscriber,
> used up 8 id's, then connected and sure enough it gave me a new range.
> However whilst using the publisher it used up all 10 in the range and then
> gave me an error message! Surely it should have automatically given me a
> new range once I'd hit 80% of the previous range.
>
>
|||There are a couple of issues here
1) are you running your agent continuously? Running is on a schedule,
every 5-10 minutes, or even less can help the adjustment, otherwise you
might want to manually execute the increment procedure
(sp_adjustpublisheridentityrange) to adjust everything
2) its not clear to me that the number of rows in the batch was close
enough to the threshold to kick off the indentity range adjustment. Was it?
3) The allottment of ranges is not always intuitive. For instance if I
set a range on the Publisher of 100, the Publisher may "own" 0-200, where
the Subscriber "owns" 200-300. You have to look at the check constaints on
the indentity range tables to figure this out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:308EF6B0-3714-4D95-8BE6-4EBD352B1929@.microsoft.com...
> Hilary
> I do intend to use much larger ranges, however I was just testing out the
process on a smaller range to see it in action. It worked for the
subscriber but not the publisher?[vbcol=seagreen]
> Any thoughts?
> Regards
> Adrian
> "Hilary Cotter" wrote:
batches.[vbcol=seagreen]
not[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
on[vbcol=seagreen]
subscriber,[vbcol=seagreen]
then[vbcol=seagreen]
a[vbcol=seagreen]