SQLTeam.com | Weblogs | Forums

Replication Error


Distribution agent job fails with below error message for Transactional replication.

Cannot drop the table 'dbo.XYZ' because it is being used for replication.


Self explanatory. If a table is being replicated, it cannot be dropped.
What you can do is remove the article from the publication and then drop the table. Removing the article does not impact the other articles in the publication; you won't need to do a new snapshot.


we need the table to be replicated.


Sounds like you're trying to use a table as a subscriber that is already a publisher. Be very careful with that.

Take a look at the article properties, Destination Object, Action if name is in use. Default is drop and recreate object. If that object is published, you can't drop it without first removing it from the publication.