August 1, 2006 at 4:04 am
I have a merge replication setup, which is managing the identity ranges of a table. However I need to manually insert some identities. Before replication in place I could just:-
set identity_insert Table_Foo on
insert into Table_Foo (ID) values (1234)
set identity_insert Table_Foo off
Now when I try this I get the error:
The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'DB_Bar', table 'Table_Foo', column 'ID'. Sp_adjustpublisheridentityrange can be called to get a new identity range.
This seems to be because the ID I'm inserting is outside of the current identity range that's been allocated by the publisher. If I insert an ID within the allocated identity range, it all works fine. But that doesn't help me.
So how do you force insert an indentity that is outside of the currently allocated identity range?
August 2, 2006 at 10:03 am
You are getting the error because your inserted identity violates a check constraint. For some reason, something is intercepting the normal constraint violation error message and replacing it with the error that you got. The constraint probably looks like:
repl_identity_range_pub_XXXXXXXX_XXXX_XXXX_XXXX_XXXXXXXXXXXX. The XXXs are a GUID.
This constraint is placed and maintained by the merge agent(s) and the sproc mentioned in the error message.
You could drop and replace the constraint with the inserts in between, but I'm not sure it is safe to do so. I would imagine, however, that (1) As long as you are certain that the inserted rows will not conflict with any of the ranges that are reserved for the merge subscribers and (2) that nothing else will insert rows at the publisher while you are doing this, then it should be OK.
You might want to test it out somewhere first.
hth and hidbn (hope this helps and hope it don't break nothin')
jg
August 3, 2006 at 12:44 am
If you want to insert an identity value outside the identity ranges available, reseed the identity range to the identity value where you want to generate and then insert the values switching the identify off.
See DBCC CHECKIDENT on how to reseed the values.
rangark
August 3, 2006 at 6:51 am
rangark, that might work, but I don't recall ever seeing DBCC CHECKIDENT performing any modifications to the repl_identity_range_pub_.... constraints, which are set by the inner workings of merge replication.
jg
August 15, 2006 at 8:28 am
Yep, that worked a treat.
I generated a script to recreate the constraints after I had dropped them. The id's I was inserted didn't clash with any indentity ranges than had been assigned to subscribers so it was safe in that respect.
Thanks.
Arthur
August 15, 2006 at 10:53 am
Glad I could help.
jg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply