Could not find stored procedure "sys.sp_cdc_enable_db_internal"

0
votes

I'm new to CDC so I started investigating the stored procedures that enable CDC in an SQL Server database. Examining sys.sp_cdc_enable_db and I get the message Could not find stored procedure sys.sp_cdc_enable_db_internal. I've looked all over my database and in the master and msdb databases. Please could anyone tell me why SSMS can't find this stored procedure?

1
sqlsql-server

1 Answers

0
votes

The feature is available only in SQL Server Enterprise and Developer editions, starting with. It can be enabled only using system stored procedures. SQL Server Management Studio provides a wide range of code templates for various feature related actions

To open the templates:

  1. In the SQL Server Management Studio menu, open View
  2. Click Template
  3. Open SQL Server Templates
  4. Open the Change Data Capture sub-folder. The T-SQL templates for administration, configuration, enumeration and meta data querying are available enter image description here

To set up the feature:

Make sure SQL Server Agent is running. If not, right-click it in Object Explorer and click Start

To enable the feature on the database, open the Enable Database for CDC template in the Configuration sub-folder, and replace the database name with the name of the database you want to track

USE AdventureWorks2012
    GO
EXEC sys.sp_cdc_enable_db
    GO

The login used must have SQL Server sysadmin privileges and must be a db_owner of the database. Otherwise, you’ll get the following error

sg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193 The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.

Vist For More Information

https://solutioncenter.apexsql.com/enable-use-sql-server-change-data-capture/