Errors when SQL Server Snapshot Replication is Running

The following errors may occur when SQL Server Snapshot replication is running:

Cannot DROP TABLE <Table Name> it is being referenced by object <View Name>.
Cannot TRUNCATE TABLE <Table Name>because it is being referenced by object <View Name>.

Data changes are not tracked for snapshot replication, each time a snapshot is applied, it completely overwrites the existing data. When using snapshot replication, it will truncate the tables before applying the data.

If the view which the error is referring to it is created with SCHEMA BINDING.  When a CREATE VIEW statement is executed SCHEMA BINDING tells SQL Server to check for dependencies and disallow any modifications that would violate them. Therefore it does not allow you to Drop/Truncate tables.

There are two ways to over the issue.
1. Remove the SCHEMA BINDING option from the view. However, for the indexed Views you will not be able to drop the SCHEMA BINDING option.
2. Drop the view before replication and perform the replication. Replication will then recreate the view.



Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |