How to fix Failed to check for changes in table 'TableName' due to exception: Microsoft.Data.SqlClient.SqlException

Question

We have enabled Change tracking on a table (say Table_A) and built Azure SQL Trigger function to track changes.

Few days later, we have added a new column (say Column_A) to the Table_A and re-deployed the Azure SQL Trigger function app.

This working for while with no issues for few days. After few days later, we receiving the below error

Failed to check for changes in table 'dbo.Table_A' due to exception: Microsoft.Data.SqlClient.SqlException. Exception message: Invalid column name 'Column_A'

To resolve this, we had to Disable change tracking, Drop GlobalState and Lease Table created in az_func schema, enable change tracking on the table and deploy the azure SQL Trigger function app.

Please help to identify what is causing the issue in the first place.

Thanks for help in advance.

Answer 1

@Kalyan Babu Basa ,

The issue you are encountering, "Failed to check for changes in table 'dbo.Table_A' due to exception: Microsoft.Data.SqlClient.SqlException. Exception message: Invalid column name 'Column_A'," is likely due to the fact that after adding a new column to the table and redeploying the Azure SQL Trigger function, the change tracking mechanism may not have been properly updated to recognize the new column.

When you add a new column to a table that is being tracked by change tracking, it is essential to ensure that the change tracking metadata is in sync with the current structure of the table. If the metadata does not reflect the new column, it can lead to SQL exceptions like the one you are experiencing.

To resolve this issue, you correctly disabled change tracking, dropped the GlobalState and Lease Table, and then re-enabled change tracking. This process forces the system to refresh its metadata and recognize the new column, allowing the trigger function to operate without errors.

In summary, the root cause of the issue was likely the change tracking metadata not being updated to include the new column after it was added to the table.

---

References:

• Change data capture (CDC) with Azure SQL Database