December 8, 2008 at 7:01 am
I am setting up merge replication so that a laptop that isn't always connected to the network can update and receive updates from a server database. I found out I should use merge replication for this so this is my first attempt at setting up replication. I've set up merge replication between a SS2K5 publisher/distributor and a SS2K5 Express pull subscriber. I have gotten it to replicate updates to records that are applied to either database, but when I try to insert a record on either database, I get an error like this one:
This failure can be caused by a constraint violation. Explicit value must be specified for identity column in table 'tablename' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
There is an identity column that is used as the primary key on the table in question. I have tried setting NOT FOR REPLICATION to Yes and No. This error comes up when it is set to yes, a slightly different error comes up when it is set to no.
I haven't found anything useful while searching for this example, does anyone have any ideas?
Thank you
December 8, 2008 at 7:09 am
For merge replication you need to have Not_For_Replication set to ON, this means that the value will not generate a new id when it is merged replicated.
It seems like you are having and issue with your publisher and subscriber generating the same ID values and when these are merged together conflicts will occur.
The way that is recommended to fix this is to have the tables seeding different Id ranges, for example the publisher could use Odd numbers for the Id, and the Subscriber could use Even values this way you can be sure there will be no conflicts.
Another way would be to give the publisher an Identity range of 1-1,000,000 and the subscriber a range of 1,000,000 - 2,000,000.
Which method is chosen depends on how your data is used and how many subscribers you plan to have.
December 8, 2008 at 7:32 am
I do have it set to ON, but I had tried setting it to OFF after misreading an article. SQL Server is managing the identities automatically. For instance, on the publisher, dbcc checkident returns:
Checking identity information: current identity value '4060', current column value '8055'.
On the subscriber, I get:
Checking identity information: current identity value '52054', current column value '52054'.
The 8055 was a former identity value on the subscriber but I have reinitialized it a few times and it seems to keep moving the identity value up. Any other ideas of what I can check?
December 8, 2008 at 8:05 am
Did you re-build the merge replication after you changed the value of NOT FOR REPLICATION to On?
The constraints that merge replication add to the table may be causing this problem, have a look at the constraints to see if you are violating them when replicating
December 8, 2008 at 8:46 am
Thank you for the quick reply. I just rebuilt the replication and it reset the identity IDs. I initialized the subscriber and checked the constraint on my table on the subscriber. It is allowing IDs between 10055 and 12055. I added a new record to the publisher database and it had an ID of 8056. When I ran the synchronization, I got the same error again. There shouldn't be any collisions on the ID since the constraint would keep me from adding any IDs that would collide with the publisher. It wouldn't be stopping the record from being added even though the constraint says CHECK NOT FOR REPLICATION in it, would it?
December 8, 2008 at 9:11 am
What are the constraints on the publishing table?
December 8, 2008 at 9:35 am
On the publishing table, the constraint on the ID is greater than 8055 and <= 10055. I've also been playing around with things and I noticed that if I try to copy a table with no triggers on it (other than the replication triggers, I have an INSTEAD OF INSERT and an AFTER UPDATE trigger on the table in question) things work fine. If I take out the INSTEAD OF INSERT trigger, but leave the AFTER UPDATE one, it works too. I think this might be where the problem is. Any idea why?
December 8, 2008 at 11:42 am
Well, removing these user created INSTEAD OF INSERT triggers has solved the issue for anyone else that comes across this topic. Thank you for your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply