Identity reused problem

I have a table with id an identity column.
Can I forbid the identity column of taking the same values of deleted
ones?

How can I resolve this issue?

*** Sent via Developersdex http://www.developersdex.com ***"Marie-Christine Bechara" <marie-christine.bechara@.ifsal.comwrote in
message news:45f57272$0$502$815e3792@.news.qwest.net...

Quote:

Originally Posted by

>
>
I have a table with id an identity column.
Can I forbid the identity column of taking the same values of deleted
ones?
>
How can I resolve this issue?
>
*** Sent via Developersdex http://www.developersdex.com ***

An Identity is always an increasing value unless you do a DBCC CHECKIDENT
call.

So already it avoids doing this.

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||"Marie-Christine Bechara" <marie-christine.bechara@.ifsal.comwrote in
message news:45f57272$0$502$815e3792@.news.qwest.net...

Quote:

Originally Posted by

>
>
I have a table with id an identity column.
Can I forbid the identity column of taking the same values of deleted
ones?

There is no foolproof way since SET IDENTITY INSERT ON will allow reuse.

You could leave the records in place, using a deleted flag column to
indicate a logical deletion.

You could use an insert trigger to intercept id's that are below the max(id)
value.

You could use stored procedures and permissions to block the usage of the
identity insert.

Quote:

Originally Posted by

>
How can I resolve this issue?
>
*** Sent via Developersdex http://www.developersdex.com ***

Identity Range reused by subscriber

We are running Windows 2000 server SP3a for the publisher/distributor and
MSDE with SP3a for the subscriber.
We have several tables that have identities for primary keys, and our
identity range handling has been working great, until now.
One of the tables decided to REUSE its existing range of 1000 (threshold is
set to 80%), so all the transactions are backing up at the subscriber since
the range
has already been used once before.
I tried to use the sp_adjustpublisheridentityrange, and it says it ran
successfully, but it is still doing the same thing.
Looking at the next available identity range on the table, it is showing
correctly for the next available block of 1000.
What could have happened, and how do I recover?
Thanks,
RS
It is hard to say what has caused this to happen. Obviously automatic identity range management has failed you for some reason.
I'd call up PSS on this problem.
Some people have had success my manually adjusting the constraint on the subscriber/publisher tables, and manually adjusting the values of MSrepl_identity_range in the distribution database.
And Mspub_identity_range.
Most DBA's will use the set it and forget approach to automatic identity range management. They will set their ranges once with what they feel are representative ranges for the lifecycle of the project. They don't have to worry about batch updates blowing
the range, or problems with automatic identity range management this way.
Of course if you are continually adding new susbcribers this might now work for you.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html