Identity Range Problem

Hi guys - A client just encountered a problem where he could not insert
into a table because of pk constraints. The database is a replica of a merge
publication with automatic identity range with settings:
Range at Pub : 1000
Range at Sub : 1000
Thresh.: 80
When I checked what the next identity was going to be, it returned '12'
but it should really be over 1000. As a matter of fact, it seems that all
tables seem to be using the Publisher's range.
Does anybody know what could have caused this?
What are the side-effects if I reseed the tables?
Thanks - Maer
You probably have a check constraint in place which is limiting the range of
values which can be inserted. Automatic identity range management had a
nasty habit of lingering after the subscription was dropped you might be
running into this - if so I would delete this constraint or adjust it.
I don't really understand what you mean by replica? Do you mean its a
subscriber, or you someone made a replica of the publication database
(through a backup perhaps).
You might also want to review this article -
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Maer" <maer@.auditleverage.com> wrote in message
news:%23VIoj$%23NGHA.420@.tk2msftngp13.phx.gbl...
> Hi guys - A client just encountered a problem where he could not
> insert into a table because of pk constraints. The database is a replica
> of a merge publication with automatic identity range with settings:
> Range at Pub : 1000
> Range at Sub : 1000
> Thresh.: 80
> When I checked what the next identity was going to be, it returned '12'
> but it should really be over 1000. As a matter of fact, it seems that all
> tables seem to be using the Publisher's range.
> Does anybody know what could have caused this?
> What are the side-effects if I reseed the tables?
> Thanks - Maer
>
|||Hi Hilary - Thanks for your response. I should have said subscriber
instead of replica.
It turned out that the client was still in SP 1 and I heard there were
some issues with identity ranges prior to SP 3. So we applied the latest SP
and dropped all subscriptions and re-subscribed. So far it seems to be
working.
It is good to know the issue with triggers so that this is one more
thing to check if that happens again.
Thanks - Maer
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uCviJ1BOGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You probably have a check constraint in place which is limiting the range
> of values which can be inserted. Automatic identity range management had a
> nasty habit of lingering after the subscription was dropped you might be
> running into this - if so I would delete this constraint or adjust it.
> I don't really understand what you mean by replica? Do you mean its a
> subscriber, or you someone made a replica of the publication database
> (through a backup perhaps).
> You might also want to review this article -
> http://www.simple-talk.com/2005/07/05/replication/
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Maer" <maer@.auditleverage.com> wrote in message
> news:%23VIoj$%23NGHA.420@.tk2msftngp13.phx.gbl...
>