An error occurred during the execution of DDL with variables while migrating the database using CDC.

Question

An error occurred during the execution of DDL with variables while migrating the database using CDC.

76340391 60

We are performing a database migration for a SQL Server upgrade.
After enabling CDC in SQL Server 2016, an error occurred in the SQL query for rebuilding an index with a variable, called by the database maintenance batch.

Must declare the scalar variable "@number". The transaction ended in the trigger. The batch has been aborted.

We believe the error is caused by the following, but please point out any misunderstandings or provide additional information:

Enabling CDC automatically creates a DDL trigger.
The error occurred when executing an ALTER INDEX SQL query using a variable, resulting in a failure to interpret the DDL trigger's SQL.

We are considering the following two solutions, but please point out any misunderstandings or provide additional information:

Solution 1) Temporarily disable CDC only when executing the maintenance DDL that causes the error, and re-enable CDC after the batch finishes.
Assuming only maintenance DDL that does not involve table definition changes such as index rebuilding is executed, data migration should resume normally, including the data that was paused, after CDC is restarted.
On the other hand, if table definitions or column definitions are changed while CDC is paused, an error will occur after CDC is restarted.
Option 2) Disable DDL triggers during the DB migration period.
There should be no particular problem if no changes are made to table definitions or columns during the DB migration period.

Also, it seems that errors don't always occur with DDL that includes variables.

I have no idea why it sometimes errors and sometimes doesn't.

Do you have any information?

*This text was translated using Google Translate.

Apologies if the text is difficult to read.

Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-06-15T15:48:02.76+00:00

What does "SELECT @@version" report?

Do you have the full error message? That is also the header part, from where we can see where the error occurred?

DDL can generally not include variables, and certainly not ALTER INDEX as I recall. You may be building the query dynamically, but the resulting query does not have any variables. Of course, you may incorrectly use a variable in an ALTER INDEX statement, and this would result in an error. But that would happen with or without CDC. So it would help with some clarification before we try to answer any questions.

Answer accepted by question author

0 additional answers

Your answer

Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-06-15T15:48:02.76+00:00

What does "SELECT @@version" report?

Do you have the full error message? That is also the header part, from where we can see where the error occurred?

DDL can generally not include variables, and certainly not ALTER INDEX as I recall. You may be building the query dynamically, but the resulting query does not have any variables. Of course, you may incorrectly use a variable in an ALTER INDEX statement, and this would result in an error. But that would happen with or without CDC. So it would help with some clarification before we try to answer any questions.

Answer 1

Why the error happens ("Must declare the scalar variable...")

You are correct that enabling CDC creates a database-level DDL trigger (tr_MScdc_ddl_event). However, your theory about the "failure to interpret the DDL trigger's SQL" is slightly off. The trigger itself compiles perfectly.

The failure happens because of scope isolation:

An ALTER INDEX statement counts as a Data Definition Language (DDL) command. When your maintenance script executes ALTER INDEX, the synchronous CDC DDL trigger fires. SQL Server executes DDL triggers in a separate, internal batch scope. Because local scalar variables (like @number) are strictly isolated to the exact batch where they are declared, the separate execution scope forced by the trigger cannot "see" your @number variable. When the trigger captures the event data, the parent statement fails parsing in that nested scope, resulting in the runtime error and aborting your transaction.

Feedback on Your Solutions

Review of Solution 1: Temporarily disable CDC

Critical Warning: Avoid using sys.sp_cdc_disable_db or sys.sp_cdc_disable_table. Disabling CDC at the database or table level will completely drop your tracking tables (_CT) and delete all captured historical data. When you turn it back on, it starts fresh, losing everything you wanted to migrate.
The Nuance: If you meant pausing the CDC SQL Server Agent capture jobs, that will not work either. The jobs only process DML (INSERT/UPDATE); pausing them does not disable the synchronous DDL trigger.

Review of Solution 2: Disable DDL triggers during the migration period

Assessment: This is a highly effective, standard infrastructure-level workaround for migration maintenance windows.
Your Caveat Check: You are completely correct. As long as no structural changes to table definitions (like ALTER TABLE adding/dropping columns) are performed while the trigger is disabled, no tracking data or schema history will be missed. Index rebuilding does not alter tracked column structures, making it safe.

You may try following fix:

Option A: Cleanly Toggle the Specific Trigger (Fastest Infrastructure Fix)

Instead of touching CDC itself, disable and re-enable only the specific DDL trigger causing the scope break at the beginning and end of your maintenance batch:

-- 1. Disable the specific CDC DDL trigger before running index maintenance
DISABLE TRIGGER tr_MScdc_ddl_event ON DATABASE;
-- 2. Run your index rebuild/maintenance batch here
-- (The @number variable will no longer trigger a scope fault)
-- 3. IMMEDIATELY re-enable the trigger once maintenance completes
ENABLE TRIGGER tr_MScdc_ddl_event ON DATABASE;

Option B: Fix the Maintenance Script's Dynamic SQL

If you can edit the maintenance script code, modify the ALTER INDEX logic to either inline the scalar value using string concatenation or safely pass the variable scope using sp_executesql: sql

-- Fix Example using parameterized execution:
SET @sql = N'ALTER INDEX ALL ON MyTable REBUILD WITH (MAXDOP = @number)'; 
EXEC sp_executesql @sql, N'@number INT', @number = @number;

Answer 2

The behavior is explained by CDC’s DDL tracking and its limitations, not by a general “CDC + variables” rule. The context provides these key points:

CDC is sensitive to metadata changes on tracked tables.
Certain DDL patterns on CDC-enabled tables are unsupported or partially supported and can cause CDC errors or failures in the capture process.
When CDC metadata or CDC-related objects are modified or missing, CDC can fail and abort the batch.

From the provided documentation:

CDC relies on internal metadata (cdc schema, change tables, system procedures, etc.) and a capture process that interprets DDL changes on tracked tables.
The “Metadata modified” troubleshooting section explicitly states that CDC must not have its metadata manually altered and that when CDC metadata is broken, the fix is to disable and re-enable CDC for the database and then re-enable for tables.
There is also a specific limitation: using variables with partition switching (ALTER TABLE ... SWITCH TO ... PARTITION ...) on CDC-enabled tables is not supported.

Although the context does not explicitly state that all DDL with variables is unsupported, it does show that:

Some DDL patterns with variables are not supported in CDC scenarios (partition switching example).
When CDC cannot correctly interpret a DDL operation on a tracked table, the capture process or associated logic can fail, which may surface as trigger-related or batch-aborting errors.

This aligns with the observation that:

DDL with variables sometimes works and sometimes fails: only specific patterns (for example, certain forms of partition switching or other metadata-affecting DDL) are problematic, not all variable usage.

Regarding the proposed solutions:

Temporarily disabling CDC around maintenance DDL

The documentation repeatedly recommends disabling and re-enabling CDC when performing schema changes that CDC cannot handle (for example, certain ALTER COLUMN operations that change data type or precision). The pattern is:
- Disable CDC for the table (or database).
- Perform the DDL.
- Re-enable CDC.
This is presented as the supported mitigation for problematic DDL on CDC-enabled tables.
The important caveat from the docs is that if table/column definitions are changed while CDC is disabled, CDC will not capture those changes, and depending on the type of change, CDC may later encounter errors or inconsistencies when re-enabled.
Therefore, the assumption is correct: if only index maintenance (no table/column definition changes) is done while CDC is disabled, and CDC is then re-enabled, CDC should resume normally. If table/column definitions are changed while CDC is disabled, CDC may later fail or require a full disable/re-enable cycle for the database and tables.

Disabling DDL triggers during the migration period

The context does not explicitly describe disabling user DDL triggers as a CDC workaround, but it does emphasize that CDC’s own metadata and objects (including CDC schema objects) must not be modified.
If the error is actually coming from a custom/user DDL trigger that is not CDC’s internal logic, disabling that trigger during migration is a valid operational choice, provided that no business logic depends on it.
However, if the error is tied to CDC’s internal DDL handling (not a user trigger), then disabling user DDL triggers will not resolve the underlying CDC limitation.

Additional points from the context that are relevant:

When CDC metadata is broken (for example, objects in cdc schema dropped or modified), the recommended fix is to disable and re-enable CDC for the database and then re-enable CDC for each table.
Import/Export or Extract/Publish operations that recreate a database will not include CDC schema, user, and is_ms_shipped=1 CDC objects, so CDC must be re-enabled after such operations.

Summary of what is supported by the context:

CDC has known limitations with certain DDL operations on tracked tables.
The recommended and supported mitigation for problematic DDL is to disable CDC before the DDL and re-enable it afterward.
If schema changes occur while CDC is disabled, CDC may later fail and require a full disable/re-enable cycle.
Some DDL patterns that use variables (for example, partition switching with variables) are explicitly unsupported with CDC.

The context does not provide a detailed internal explanation of why a specific ALTER INDEX with a variable sometimes fails with “Must declare the scalar variable” in a DDL trigger, so the exact root cause of that specific error cannot be further explained from the provided material.

References: