Problem with central subscriber model

Hi,

I am trying to acheive the central subscriber model for Sql server 2005 replication. I have two publishers and the centrol subscriber. Both publishers are Sql server 2005 Workgroup edition. The following table shows the Software I have used for the test.

SQL version

Service pack

HotFix

Publishers

SQL Server 2005 Workgroup edition

2

1

Subscriber

SQL Server 2005 Enterprise edition

2

1

I have encountered an obstacle in the replication process to achieve the Central subscriber model. I have configuration table, It exists on servers TB1 and TB2 and want to amalgamate the data to a central subscriber. There are two duplicate primary key and overlapping values existing across the 2 publisher tables. These records need to be updated during the process of replication.

TB1

Guid Name CreatDate EditDate

34a.. TEST02F 3/6/2007 3/6/2007

775.. EP109F 1/29/2007 1/29/2007

ffe.. EP109G 1/26/2007 1/26/2007

TB2

Guid Name CreatDate EditDate

d008.. MS060F 7/20/2005 11/21/2006

775.. EP109F 1/29/2007 1/29/2007

ffe.. EP109G 1/26/2007 1/26/2007

Normally, the default Article properties settings are used 'DROP existing table and re-create it', however here we don't want this setting, because the one table records will then be removed, leaving just the data from the other table. So, we modify the article properties on the snapshot tab to 'Keep the existing table unchanged'’. As result we got the PK violation error.

I have tried using -SkipError option in distribution job Replication Agent Profiles, that did not work.

Thank you for your help

Where are you getting this error? Are you getting it while the snapshot is being applied?

If so you need to create a consistent subscription which represents the data in all publishers with duplicates removed. Most people will have a location identifier in all publishers and then filter their publication on this filter (the delete all data which matches the row filter option in the name conflicts section of the snapshot tab).

If you are getting this error during the application of new commands you need to consider how unique your primary key is. GUIDs are pretty well guaranteed to be unique so I don't really understand how this could happen. You could modify your insert proc to do an existence check before it inserts the values.

|||The snapshot is going to take the contents of publisher1 and stick them into the tables on the subscriber. It will then take the contenst of publisher2 and stick that data into the same set of tables. There isn't any error with replication in this. If you can't take a table from publisher1 and a table from publisher2, smash them into a single table, and not get a primary key conflict, replication can not overcome this. A central subscriber is designed to aggregate data from 1 - N publishers into a single set of tables. That data still needs to obey the rules you set up on the tables such as PK. So, the only way this works is to ensure that you aren't trying to send anything in that causes a PK violation.sql