Author |
Topic |
shiloh
Starting Member
48 Posts |
Posted - 2007-11-30 : 14:22:00
|
Hi allI 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. |
|
|
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.. |
|
|
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. |
|
|
shiloh
Starting Member
48 Posts |
Posted - 2007-12-02 : 21:04:41
|
Hey rmiaothanks 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? |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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... |
|
|
|