Cannot Replicate Stored Procedure Execution when CDC is enabled

Published 03-17-2021 01:18 PM 745 Views
Microsoft

With Transactional Replication you can replicate execution of the Stored Procedure like this:

 

Taiyeb_Zakir_0-1616012084473.png

 

This has several advantages as discussed in this article:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/publishing-store...

 

When you enable CDC at the Database level, replication of the stored procedure execution will NOT work. This is By Design. CDC does not support tracking at stored procedure execution level, which means individual rows logged by stored proc execution need to be flagged with REPLICATE bit, which makes it impossible for transactional replication to replicate stored proc execution.  As a result, ‘stored proc execution’ will work only when transactional replication is enabled and CDC is disabled.

%3CLINGO-SUB%20id%3D%22lingo-sub-2218128%22%20slang%3D%22en-US%22%3ECannot%20Replicate%20Stored%20Procedure%20Execution%20when%20CDC%20is%20enabled%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2218128%22%20slang%3D%22en-US%22%3E%3CP%3EWith%20Transactional%20Replication%20you%20can%20replicate%20execution%20of%20the%20Stored%20Procedure%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Taiyeb_Zakir_0-1616012084473.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F264979iC2EB90BEEED2CD53%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Taiyeb_Zakir_0-1616012084473.png%22%20alt%3D%22Taiyeb_Zakir_0-1616012084473.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20has%20several%20advantages%20as%20discussed%20in%20this%20article%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Freplication%2Ftransactional%2Fpublishing-stored-procedure-execution-in-transactional-replication%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Freplication%2Ftransactional%2Fpublishing-stored-procedure-execution-in-transactional-replication%3Fview%3Dsql-server-ver15%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20you%20enable%20CDC%20at%20the%20Database%20level%2C%20replication%20of%20the%20stored%20procedure%20execution%20will%20NOT%20work.%20This%20is%20By%20Design.%20CDC%20does%20not%20support%20tracking%20at%20stored%20procedure%20execution%20level%2C%20which%20means%20individual%20rows%20logged%20by%20stored%20proc%20execution%20need%20to%20be%20flagged%20with%20REPLICATE%20bit%2C%20which%20makes%20it%20impossible%20for%20transactional%20replication%20to%20replicate%20stored%20proc%20execution.%26nbsp%3B%20As%20a%20result%2C%20%E2%80%98stored%20proc%20execution%E2%80%99%20will%20work%20only%20when%20transactional%20replication%20is%20enabled%20and%20CDC%20is%20disabled.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Co-Authors
Version history
Last update:
‎Mar 17 2021 01:17 PM
Updated by: