SSDT Publish errors on Creating Publish Preview

Question

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

When attempting to publish to one specific target database, the "Creating publish preview" step appears to fail, but no error is given. The output from the preview includes some expected warnings:

• The column {...} is being dropped, data loss could occur
• If this deployment is executed, changes to {...} might introduce run-time errors in {...}
• This deployment may encounter errors during execution because changes to {...} are blocked by {...}'s dependency in the target database

I have unchecked "Block incremental deployment if data loss might occur".

The Preview just stops, and no script is generated.

Answer 1

This happens when there exists a stored procedure (or view or constraint or other object) in the target database, that isn't included in your sqlproj, that references a table that would be altered by deploying your sqlproj. SSDT apparently can't determine whether the change is safe unless the referring thing is included in your sqlproj, and then it errs on the safe side by blocking the deployment.

Disabling the "Block incremental deployment if data loss might occur" option only relaxes the data-loss checks. There isn't a "Block incremental deployment if run-time errors might occur" option.

You have three options:

1. add whatever stored procedures, views, or whatever from the target database to your sqlproj

2. uncheck the "Verify Deployment" option in the ssdt publish options (this is dangerous unless you're aware of the other referring sprocs and know that they aren't going to break)

3. if you're certain that everything that should exist in the target database is contained in your sqlproj, you can enabled the "Drop objects in target but not in source" option

Answer 2

The issue may also be caused prepending a database object with the wrong schema. For instance a table being referenced within a stored procedure SQL statement and the table being prepended with an incorrect schema name.

Additionally, we had some permissions for a specific security group that once we removed the solution would build again. In order to troubleshoot the error perform a schema compare of the project code and the target database. Remove differences from the database until the publish functionality works. The last item that you removed from the database is your culprit.