SQL server replication conflicts

Question

Once per week, a snapshot publication is done from a database "myDB" in PROD to "myDB" in PREPROD.

Now in PREPROD we set up a transactional replication between "myDB" to another PREPROD database "myDB2".

The problem is that the transactional replication block the SNAPSHOT with the error :

Cannot truncate table 'dbo.myTable' because it is published for replication or enabled for Change Data Capture

The solution I think is deleting the transactional replication once a week, creating a snapshot replication, run it, then delete it. After that recreate the transactional replication. And doing that by scheduling scripts containing the publications/subscription .

Is there a cleaner solution to achieve that ?

Answer 1

This is likely because the Articles that are used in both your Snapshot and Transactional Publications have their Destination Object properties set to "Truncate all data in the existing object" for the Action if name is in use property:

Truncate is not allowed on tables that are bound by certain things, such as another Publication.

Instead, you can change it to the option "Delete data. If article has a row filter, delete only data that matches that filter". I believe you should only have to do this on the Snapshot Publication side (but if that doesn't work, try doing it on the Transactional Publication side too). There's also another option called "Drop existing object and create a new one", but I believe you'll run into a similar error with your current setup, so "Delete..." is probably the only option that'll work for you.