Insert records with a CHECK NOT FOR REPLICATION constraint
danddba
Posts: 2
Hi,
I have two SQL Server 2000 databases set up with Merge Replication between them. I'm using Automatic Identity Range Handling to keep the identity values unique.
I used SQL Data Compare to discover some data problems and to manually synchronize changes between the two databases... but it doesn't work. I'm getting SQL Error 548: The identity range managed by replication is full and must be updated by a replication agent.
Is there a way to insert these records?
A little more history.
SDC creates a script like this:
SET IDENTITY_INSERT [dbo].[TableA] ON
INSERT INTO TableA(ID, ...)Values( 500, ...)
SET IDENTITY_INSERT [dbo].[TableA] OFF
But this fails because TableA has a constraint that says "allow only ID's between 22000 and 24000.
=============================================
Database A - Table A and Database B - Table A have their ID column defined as such:
[ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
At the bottom of Table A is a constraint:
CONSTRAINT [repl_identity_range_sub_<...>] CHECK NOT FOR REPLICATION ([ID] > 22000 and [ID] < 24000)
Thanks,
- Dan
I have two SQL Server 2000 databases set up with Merge Replication between them. I'm using Automatic Identity Range Handling to keep the identity values unique.
I used SQL Data Compare to discover some data problems and to manually synchronize changes between the two databases... but it doesn't work. I'm getting SQL Error 548: The identity range managed by replication is full and must be updated by a replication agent.
Is there a way to insert these records?
A little more history.
SDC creates a script like this:
SET IDENTITY_INSERT [dbo].[TableA] ON
INSERT INTO TableA(ID, ...)Values( 500, ...)
SET IDENTITY_INSERT [dbo].[TableA] OFF
But this fails because TableA has a constraint that says "allow only ID's between 22000 and 24000.
=============================================
Database A - Table A and Database B - Table A have their ID column defined as such:
[ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
At the bottom of Table A is a constraint:
CONSTRAINT [repl_identity_range_sub_<...>] CHECK NOT FOR REPLICATION ([ID] > 22000 and [ID] < 24000)
Thanks,
- Dan
Comments
This sounds a bit of a tricky problem. I'd assume that the SQL Server enforces the identity range as a sanity check and reseeds the identity periodically. While there is no facility in Data Compare to do that, it may help to use the ignore identity columns setting or maybe to just not compare and synchronize this ID column, assuming it's not the primary key column.
This has the effect of allowing the identity column to assign its' own values instead of having them forced in by Data Compare. If you are going to insert more rows than 2400 minus the table's CURRENT_IDENT value, there may still be a problem that I don't know how to go about resolving.
But with any luck ignoring the identity column in Data Compare should work!