How do I add a second subscriber to SQL 2005 Transactional Replication publication

Question

I have setup SQL 2005 Transactional Replication, with one master database acting as the publisher, and one subscriber. It's running fine. Now, I want to add a second subscriber to this publication. I created the subscription on the second server, and created the snapshot ok, but when it starts to syncronize I get the error

"Cannot drop the table 'xxxxx' because it is being used for replication."

How do I add a second subscriber to an existing publication that already has one subscriber? I don't want to stop the first subscription that already exists and is working fine if possible.

Answer 1

It sounds like your target table for the second subscription is being replicated (i.e., it's already a publication). That's what the error indicates. Does the table already exist on the subscribing database?

Answer 2

I figured it out. Before syncronizing I had restored a backup of the database to the new subscriber that was enabled for replication. So most of the tables had the "Table is replicated" property set to True. I ran sp_removedbreplication on the database (on the new subscriber) and it set that property to False for all the tables which allowed the syncronization to work without errors.

thanks!