Currently when we build deployment packages we try to validate that the deployment is properly configured for the customer site to reduce errors. We do this by validating the version number, the database it is pointed to, connections are working, etc.

Using SQLCMD we get a handy function SET NOEXEC ON.  This allows us to disable the execution of the rest of the query when we flag something amiss.

Typically the sysadmin would then make the appropriate corrections and attempt to run the upgrade script again.  However, if the window is not re-enabled then nothing will happen other than the customer receiving a “command completed successfully” statement when nothing in the script actually executed.

To avoid this we typically add the NO EXEC OFF statement to the bottom of the script so that the window will be re-enabled for the customer to try again.

Problem

There are instances where due to the way compile and run-time checking works we could raise an error and disable execution, but the query will still try to validate certain things even though it is not going to execute them. In these cases  the subsequent errors may abort getting to the bottom of the script and re-enabling the window.

Example

A customer is currently at Version 3.0 of a Dashboard and wants to go to Version 3.2.

For this particular upgrade path we have them upgrade to version 3.1 and then 3.2

1. I restored a Version 3.0 Dashboard
2. I accidently try to run the Upgrade Version 3.1 to 3.2 script over 3.0.
   • 
3. We can see it gives us our warning that “The database must be at Release 3.1 for this update.” However, it also performed some additional validation even though no execution will happen.
4. Now we run the V3.0 to V3.1 script that we meant to run first
   • 
5. We see the *** UPDATE COMPLETE successfully
6. We now try to run the 3.1 to 3.2 upgrade that is still open in the other window where we tried to run it
   • 
7. The above did not actually do anything and can be misleading. We should always see the steps it took during upgrade.
8. Running the script again will actually perform the upgrade since the last run re-enabled the query window at the end
   • 

Fix

Currently on our projects we have the SET NOEXEC OFF statement at the end of the post-deployment script.

We instead need to move it to the beginning of the pre-deployment script.

Additionally the project deployment creation notes should be updated to reflect the change. Instead of specifying to make sure it is at the bottom we should have a note to move it to the top of the script with the instructions.

This change will re-enable the window at the beginning of execution if it has been disabled.

Do you have any SSDT Deployment tips and tricks?

Let us know in the comments below.