March 26, 2009 at 6:34 am
Hi,
Hope someone can help me out here. I have a situation where I want to skip a bcp error on initialization when setting up transactional replication.
I'll explain...
We have two publishers replicating to the same subscriber. If you look at schema, the two publishers are identical; data MAY differ depending on the table.
When setting up a new article from the first publisher, everything runs fine, schema is applied at the subscriber & the data is inserted via bcp. But, I have to set up the exact same article from the second publisher. In most cases the published table from the two publisher databases, have the same data.
When initializing the second publisher, I get a bcp error, violation of the primary key as it wants to insert the same data into the same table which is now also replicated from the first publisher.
So, knowing that the schema & data from these articles are identical, I want to skip the bcp error.
At the moment, I have to use one of two manual fixes. Either delete the command from the distribution database, or open the bcp file, clear out the data & save it again. Replication still goes through the normal steps, but inserts nothing into the table & all is well.
This is not a problem when it's only two publishers replicating to one subscriber, but our environment consists of a total of +-200 publishers which replicate to +-75 subscribers... You can imagine fixing this error for every subscription.
Is there a way to tell replication to ignore/skip the "error"? By error I mean the actual bcp insert.
Hope I'm making some sense here... 🙂
Thanks in advance!!
Tobie.
March 27, 2009 at 7:42 am
Have you tried not to Initialize from the second publisher? You first set up the first publisher with transactional Replication and SnapShot. The second one you set it up with the transactional replication but with no SnapShot. This tells the second one not to try to insert data. So no BCP, No Error.
Only issue is that you will not get any initial data from the second publisher.
Just my .0002 cents:-)
-Roy
March 27, 2009 at 8:27 pm
Subscribe to publisher #1 with initialization which will create the schema and snapshot the data. Subscribe to publishers #2-X without initialization.
Will new inserts into your publishers occur on only one of them or will they occur simultaneously on more than one (or all)?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply