SSDT Post errors while creating preview publication

Question

I am using Visual Studio 2013 to manage a .sqlproj file containing our database schema. The circuit has been successfully deployed dozens of times.

When you try to publish to one specific target database, the message "Create a preview of the publication" appears, but no error is indicated. The preview result includes some expected warnings:

• Column {...} is deleted, data loss may occur
• If this deployment is completed, changes to {...} can lead to runtime errors in {...}
• Runtime errors may occur in this deployment because changes to {...} are blocked by {...} the dependency in the target database

I unchecked "Block incremental deployment if data loss could occur."

The preview stops and no script is created.

Answer 1

This happens when there is a stored procedure in the target database that is not included in your sqlproj (either a view or constraint or other object) that refers to a table that will be modified by deploying your sqlproj. SSDT apparently cannot determine if the change is safe if the link is not included in your sqlproj and then it is mistakenly taken to block the deployment.

Turning off the "Block incremental deployment if data loss may occur" setting only allows you to stop checking data. There is no option to "Phase block deployment if run-time errors can occur".

You have three options:

• add any stored procedures, views or anything from the target database to your sqlproj

• uncheck the "Check deployment" in the ssdt publishing options (this is dangerous if you do not know other reference sprocs and know that they will not be interrupted)

• if you are sure that everything that should exist in the target database is contained in your sqlproj, you can enable the option "Drop objects in the target, but not in the source"

Answer 2

The last warning pattern is more than a warning:

Runtime errors may occur in this deployment because changes {...} are blocked by {...} the dependency in the target database

apparently was the culprit of stopping the rest of the preview and generating the script.

Interestingly, the introduced schema change would not violate the triggers referenced by the preview output.