Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 NOT FOR REPLICATION causing PK errors

Author  Topic 

shiloh
Starting Member

48 Posts

Posted - 2007-11-30 : 14:22:00
Hi all

I have set up Transactional Replication from 2005 to 2000. After running a few transactions I disabled the replication, pointed one of our IIS servers to the 2000 database to do a quick functional test and validate replication. We are getting "Cannot insert duplicate value in object XXXX with violation of PK constraint errors. I was able to figure out the cause. This was happening for tables with Identity columns which have been set NOT FOR REPLICATION. The IDENT_CURRENT values are different than the MAX value in the table. so doing a DBCC CHECKIDENT with RESEED seemed to have fixed the issue. Now I am running the DBCC command on ALL tables that have been used for Replication and have NFR set to true. Its taking a while as the tables are big.

Now I am wondering if anyone has faced similar issue? Is this a known issue? I have googled and have found nothing or no one complaining about this. I believe most people have used replication for reporting where they are just querying the databases. We are testing replication as a fall back scenario.

Opinions welcome..
thanks,
Don

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-02 : 01:10:29
You have to specify dirrenet seed on those tables, like use odd numbers on publisher and even numbers on subscriber.
Go to Top of Page

shiloh
Starting Member

48 Posts

Posted - 2007-12-02 : 15:04:25
I am not trying to distribute the Identity values.. I need the subscriber to be exactly same as publisher. After I stopped replication and tried to do a functional test via application on the subscriber test I was getting those PK errors..
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-02 : 16:23:37
Subscriber will have same value in that column, that's the way to handle this situation.
Go to Top of Page

shiloh
Starting Member

48 Posts

Posted - 2007-12-02 : 21:04:41
Hey rmiao

thanks for answering. but I dont understand what you are trying to say.. Can you please elaborate? My question was, the subscriber DB should be readily usable.. but we got PK errors until I manually reseed the values.. Is this a practice or is there something wrong with it?
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-02 : 22:42:56
What you need to do is that create table on publisher like this:

create table tab_name (col1 int identity(1, 2) not for replication, ...)

Create same table on subscriber like this:

create table tab_name (col1 int identity(2, 2) not for replication, ...)


That way, you can insert rows on both side without duplicated identity values.
Go to Top of Page

shiloh
Starting Member

48 Posts

Posted - 2007-12-02 : 23:33:54
The issue is with the subscriber. We have had no issues with application connecting to Publisher. Only when we connect to subscriber we see these errors.
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-03 : 22:31:16
That's why you have to set different identity seed in subscriber.
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-12-11 : 12:29:29
how was the article created on the subscriber? did you run the snapshot agent? allowing the agent to create the table on the destination? if yes then you shouldn't be getting this problem unless someone has manually inserted rows at the subscriber...
prevent that by setting explicit insert denies from all other users except the distribution agent user.

if no, you should re-run the snapshot with a drop option on the article's properties so it recreates the table

--------------------
keeping it simple...
Go to Top of Page
   

- Advertisement -