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 column: what happens when it runs out?

I have a table containing an identity column (bigint) as its primary key.
The table will have very frequent insertions/deletions from it.
Many items will be added, but they aren't really expected to be there for
long. I can never expect that the table is empty, however.
So, eventually, the new ID's that get added will increment up to the maximum
bigint value. What happens then? Does it automatically wrap and start
over?
If it starts over, how does it handle any values that may still exist?
How should I handle this?
Thanks!
--
Adam Clauss
cabadam@.tamu.eduYou'll receive an overflow error if IDENTITY reaches the upper bound of the
datatype. Is that likely with a BIGINT? Assuming you start at zero then even
if you generated 1 billion rows per second, 24x7 it would still take nearly
300 years before you hit the ceiling. Your hardware will fall apart rather
sooner! If you're still not convinced then there's always NUMERIC instead.
David Portas
SQL Server MVP
--|||Hmm.. ok, I hadn't realized that bigint ran quite THAT big...
Thanks!
--
Adam Clauss
cabadam@.tamu.edu
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Y7qdnZR3Gc1nVG_cRVn-oA@.giganews.com...
> You'll receive an overflow error if IDENTITY reaches the upper bound of
> the datatype. Is that likely with a BIGINT? Assuming you start at zero
> then even if you generated 1 billion rows per second, 24x7 it would still
> take nearly 300 years before you hit the ceiling. Your hardware will fall
> apart rather sooner! If you're still not convinced then there's always
> NUMERIC instead.
> --
> David Portas
> SQL Server MVP
> --
>|||> I have a table containing an identity column (bigint) as its primary key.
> The table will have very frequent insertions/deletions from it.
> Many items will be added, but they aren't really expected to be there for
> long. I can never expect that the table is empty, however.
Do you really need an IDENTITY column?

> So, eventually, the new ID's that get added will increment up to the
maximum
> bigint value. What happens then? Does it automatically wrap and start
> over?
No, you will get an overflow error.

> How should I handle this?
Not having an IDENTITY column?
A|||In that case maybe you don't need it. INTEGER is half the size of BIGINT and
still can store values from -2,147,483,648 to 2,147,483,647.
David Portas
SQL Server MVP
--|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OdRGx9MAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> Do you really need an IDENTITY column?
What else would I use? No other column would necessarily be unique.
Adam Clauss
cabadam@.tamu.edu|||> > Do you really need an IDENTITY column?
> What else would I use? No other column would necessarily be unique.
Oh great, another "I don't have a key so I'll make one up"... are you saying
that you could have multiple rows with the exact same data in every column?
What exactly are you trying to model?|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OgwuDLPAFHA.2804@.TK2MSFTNGP15.phx.gbl...
> Oh great, another "I don't have a key so I'll make one up"
You made MVP with that kind of an attitude? If this is the help you are
offering, I don't want any.
... are you saying
> that you could have multiple rows with the exact same data in every
> column?
That would be what I just said. Like I said though, if that's the kind of
help you are offering, thanks, but no thanks.
Adam Clauss
cabadam@.tamu.edu|||> That would be what I just said. Like I said though, if that's the kind of
> help you are offering, thanks, but no thanks.
Great, have fun. Maybe you'll get lucky and Celko won't stumble across this
one.|||If you are concerned about about running out of IDENTITY values with a
"numeric/int" based datatype, I would suggest you to look at the
UNIQUEIDENTIFIER datatype.
I am a VERY VERY VERY firm believer in the UNIQUEIDENTIFIER datatype (aka
the GUID [Globally Unique Identifier] in .NET and elsewhere). I resisted it
at first... but came around to understand it and what it could do for my
applications...
It will take a bit change on your part to convert to the GUID way of
thinking, but it might be worth it for you. I know it was for me but well
worth it!!!
Chris
"Adam Clauss" <cabadam@.nospam.tamu.edu> wrote in message
news:uKir3hMAFHA.2600@.TK2MSFTNGP09.phx.gbl...
>I have a table containing an identity column (bigint) as its primary key.
>The table will have very frequent insertions/deletions from it.
> Many items will be added, but they aren't really expected to be there for
> long. I can never expect that the table is empty, however.
> So, eventually, the new ID's that get added will increment up to the
> maximum bigint value. What happens then? Does it automatically wrap and
> start over?
> If it starts over, how does it handle any values that may still exist?
> How should I handle this?
> Thanks!
> --
> Adam Clauss
> cabadam@.tamu.edu
>