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.

Octahedrite

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

Mccurry

4. Manually add / remove object from the database so that dacpac doesn't find a difference. I assume actions such as dropping a column are rather one-time. – Duodecimal

This is correct. I would also suggest do schema compare to find difference between source and target database. For me it was statistics which were auto created in the database. Dropping them worked for me ! – Concessionaire

I had this with a schema-bound view. Had to write logic to enumerate all schema-bound views, retain their script, drop them, execute the SSDT automated deployment, and re-create the previously dropped views afterwards. – Pronominal

One more thing: check or one of your scripts accidentally not marked as "not in build". I had " if this deployment is executed unnamed will be dropped and not re-created" exception because of this. – Catty

If this is the solution, then you are in big troubles if you use replicas, I started to get this issue because replicas created insert triggers, I get the "are blocked by" error. I can tell you it is NOT an option to include replica DSS objects into project, the replica objects changes between environments, they have different guid_names, so if you have a big infrastructure, you are doom. So far looks like I need to drop and recreate every failing object, which is crazy. – Impugn

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.

Puett

Answer 3

The last warning pattern appears to be more than a warning:

This deployment may encounter errors during execution because changes to {...} are blocked by {...}'s dependency in the target database

appears to have been the culprit behind stopping the rest of the preview and the generation of the script.

Interestingly, the schema change being introduced would not have broken the triggers referenced in the preview output.

Octahedrite

Answer 4

removing schemabinding from the view allows the publish to succeed with only warnings

Yorgo