Replication and Primary Keys

[jeffmoore64]

Hi,
I'm replicating a 2005 db to a 2000 db. On the pub I set to false every property. ie copy indexes, copy keys, etc.
Yet every time I run a snapshot the replication fails with the error, primary key already defined.
I have to drop the primary keys on all my subs tables and re-run the snapshot. Then it completes. But it also puts the PKs back on my subs tables. Is there anyway around this?
TIA

 

[ptheriault]

You have to go into the properties for each article in the publication and change those settings.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

I have and every property is set to false. I have double and triple checked every table (only 11). Every property is set to false. Yet I still get the PK error.

 

[ptheriault]

Oh,
Ok here is the deal. In order for a table to participate in replication it must have a pk. So a snap shot will re-create your pk's. You should make sure that the snap shot drops the table or PK before applying the new snapshot.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

Okay. I understand all that. I cant drop the table as there are triggers on the subs tables. I have been manually dropping the PK before I run my snapshot. I'd love to find a way to do this automagicly...

 

[mrdenny]

When setting up the replication there is an option to have the snapshot agent run a script file against the subscriber before adding the subscription. You can use this to run a T/SQL script which will drop the primary keys on the tables then deploy the subscription, then put the keys back.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com