Azure SQL Database Deployment Error after enabling SQL data sync

Question

Azure SQL Database Deployment Error after enabling SQL data sync

Aparna Manoharan 40

After enabling Azure SQL Data sync, I am getting below warning while executing DACPAC and deployment if getting failed. I want to Ignore these errors and continue with deployment.

Error SQL72032: If this deployment is executed [Foreign key] will be dropped and not re-created.

Error SQL72031: This deployment may encounter errors during execution because changes to <table> are blocked by [table_dss_delete_trigger]'s dependency in the target database.

I have used below publish action to Ignore verification errors and for not dropping objects not in source. /p:TreatVerificationErrorsAsWarnings=false /p:BlockOnPossibleDataLoss=False /p:DropObjectsNotInSource=False /p:DropConstraintsNotInSource=false /p:DropIndexesNotInSource=false /p:DropDmlTriggersNotInSource=false /p:ScriptDatabaseOptions=False

Still getting error when deploying DACPAC

Sai Raghunadh M 4,570 Reputation points Microsoft External Staff Moderator

2025-02-06T20:51:33.34+00:00

Hi @Aparna Manoharan

Thanks for the Question and using Microsoft Q&A.

As per my understanding you are still facing errors even after publishing some actions to ignore verification errors.

please try the below publish action that might resolve the errorIf this does not resolve your issue, please share the exact error you are encountering so we can investigate further and assist you.

Meanwhile please refer the below Documentation that might help you:

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver16

I hope this information helps, please do let us know if you have any Queries
Aparna Manoharan 40 Reputation points

2025-02-07T11:44:40.69+00:00

Hi ,

I tried the above publish action, but im getting these errors:

Error SQL72032: If this deployment is executed [Foreign key] will be dropped and not re-created.

Error SQL72031: This deployment may encounter errors during execution because changes to <table> are blocked by [table_dss_delete_trigger]'s dependency in the target database.

Please help!
Sai Raghunadh M 4,570 Reputation points Microsoft External Staff Moderator

2025-02-07T13:26:41.8266667+00:00
Hi @Aparna Manoharan

It seems like the deployment is being blocked by a foreign key constraint and a delete trigger

Before deploying the DACPAC, disable the table_dss_delete_trigger. You can re-enable it after the deployment is complete

Manually modify the deployment script to handle the foreign key constraint. You might need to drop the foreign key constraint before deployment and recreate it afterward.

Please try adding these:

/p:IgnoreDdlErrors=True

/p:IgnoreDmlTriggers=True

I hope this information helps, please do let us know if you have any Queries

1 answer

Your answer

Sai Raghunadh M 4,570 Reputation points Microsoft External Staff Moderator

2025-02-06T20:51:33.34+00:00

Hi @Aparna Manoharan

Thanks for the Question and using Microsoft Q&A.

As per my understanding you are still facing errors even after publishing some actions to ignore verification errors.

please try the below publish action that might resolve the errorIf this does not resolve your issue, please share the exact error you are encountering so we can investigate further and assist you.

Meanwhile please refer the below Documentation that might help you:

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver16

I hope this information helps, please do let us know if you have any Queries
Aparna Manoharan 40 Reputation points

2025-02-07T11:44:40.69+00:00

Hi ,

I tried the above publish action, but im getting these errors:

Error SQL72032: If this deployment is executed [Foreign key] will be dropped and not re-created.

Error SQL72031: This deployment may encounter errors during execution because changes to <table> are blocked by [table_dss_delete_trigger]'s dependency in the target database.

Please help!
Sai Raghunadh M 4,570 Reputation points Microsoft External Staff Moderator

2025-02-07T13:26:41.8266667+00:00

Hi @Aparna Manoharan

It seems like the deployment is being blocked by a foreign key constraint and a delete trigger

Before deploying the DACPAC, disable the table_dss_delete_trigger. You can re-enable it after the deployment is complete

Manually modify the deployment script to handle the foreign key constraint. You might need to drop the foreign key constraint before deployment and recreate it afterward.

Please try adding these:

/p:IgnoreDdlErrors=True

/p:IgnoreDmlTriggers=True

I hope this information helps, please do let us know if you have any Queries

Answer 1

Aparna Manoharan 40

I added these publish action

'/p:IgnoreDdlErrors=true /p:IgnoreDmlTriggers=True /p:IgnoreWithNocheckOnForeignKeys=true /p:TreatVerificationErrorsAsWarnings=false /p:BlockOnPossibleDataLoss=true /p:DropObjectsNotInSource=False /p:DropConstraintsNotInSource=false /p:DropIndexesNotInSource=false /p:DropDmlTriggersNotInSource=false /p:ScriptDatabaseOptions=False'

But got these errors:

##[error]*** 'IgnoreDdlErrors' is not a valid argument for the 'Publish' action.

##[error]*** 'IgnoreDmlTriggers' is not a valid argument for the 'Publish' action.

Sai Raghunadh M 4,570 Reputation points Microsoft External Staff Moderator

2025-02-07T17:26:46.4033333+00:00

Hi @Aparna Manoharan

Thanks for your response.

As these public actions doesn't resolve the issue, please try to drop the foreign key constraint before deployment and recreate afterwards.

please look into this similar thread that might help you in excluding foreign keys during publish action.

https://dba.stackexchange.com/questions/248953/how-to-exclude-foreign-keys-during-publish-action-in-sqlpackage

https://stackoverflow.com/questions/28618386/ssdt-publish-errors-on-creating-publish-preview
Sai Raghunadh M 4,570 Reputation points Microsoft External Staff Moderator

2025-02-10T02:34:16.8+00:00

Hi @Aparna Manoharan

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sai Raghunadh M 4,570 Reputation points Microsoft External Staff Moderator

2025-02-11T02:45:27.4433333+00:00

Hi @Aparna Manoharan

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Azure SQL Database Deployment Error after enabling SQL data sync

1 answer

Your answer