"Eric.Li" <anonymous@microsoftnews.org> wrote in message
news:ufUvuhoEEHA.580@TK2MSFTNGP11.phx.gbl...
> I have multiple tables with identity column replicated (merge
> replication) from Server A to Server B. During the initial setup, I
> specified that sql agent manages the identity columns automatically and
> runs in continuous mode. But a while, I got identity error and I had to
> stop the agent, run sp_adjustpublisherientityrange and restart the
> agent. After more research, I found out that this is a SQL bug and what
> Microsoft recommend is exactly what I did, they also said the agent
> should be scheduled to run every minute or so. However, in my case, this
> is unacceptable because client may still run out of ID before the agent
> restart and it causes the application error out. So here's what I did
>
> 1) build a insert trigger on every articles to check the range and call
> sp_adjustpublisheridentityhrange is necessary, but this solution doesn't
> work because looks like sp_adjustpublisheridentity has no effect until
> the identity column hits its limit
>
> 2) build a insert trigger on every articles to check and re-adjust the
> check constraint. This somewhat working on the publisher side, however,
> how to deal with in at the subscriber side? I know there's sp_help and I
> can get the constraint from system tables, but they are more for human
> eyes than for programs, just pain to parse and I just don't like putting
> too much inside a trigger
>
> any recommendations?
>
> Thanks
>
> --
> Eric Li
> SQL DBA
> MCDBA