VSDB: Tracking down Deploy errors

I’m working on a project that has thousands of lines of post-deployment scripts. Once I have done a successful build, I still sometimes get errors in the deploy, usually indicating a problem in one of my post-deployment scripts. Unfortunately, Visual Studio Database Edition rarely gives me enough information to easily identify the location or source of the error.

I believe the problem is two-fold. First off, any output from PRINT statements that may help you locate the error might be useless – I believe that the PRINT output is buffered and that when the error occurs, the buffer might not be flushed. So there may still be a bunch of PRINT output between what you see and when the error really occurs (hey, if anyone from the VSDB team is reading this, please fix this in VS.NET 2010!). The second problem is that VSDB does not locate the error for you and tell you which file and what line the error is on. That’s probably because it concatenates all of the post deployment scripts together with the database creation script into one huge SQLCMD file.

So, what's a developer to do?

Once of the fastest ways to figure out the issue is to leverage another tool that will show you ALL of the PRINT output as well as tell you what line of code the error occurs on: SQL Server Management Studio.

When you do a deploy, VSDB creates a big script that creates the entire database – this script also includes all of the contents of the post-deployment scripts. You want to run this script in SSMS to find out where the error is. Then, find that same piece of code in the project (probably in the post-deployment scripts section). Hopefully you will quickly recognize it once you see it.

Here's what to do:

1. Locate the database project's folder (your working folder on your PC). If you're not sure you can find it here:
   

2. Navigate to the "sql" folder:
   

3. Then navigate to the folder corresponding to the Build Configuration you used, e.g. "debug":
   

4. Find the .sql file that matches the name of your deployment database (you specified this in your database project's properties' Deploy tab at some point, otherwise it might default to the name of the project):
    

5. Open that file in SQL Server Management Studio.

6. Note that this .SQL file is a "SQLCMD" file, using special syntax with commands that start with ":" to set variables etc. Don't worry about this too much, but once the script is open in SSMS, be sure to toggle on SQLCMD in the Query menu:
   

7. Make sure you are connected to the correct SQL Server\Instance before you run the script. It will probably drop and re-create the database you had specified, just like the Deploy command would, so keep that in mind. Note that you can tweak a couple things if you want – changing the DatabaseName variable for example, and removing the specification of the MDB/LDF file names from the database creation.

8. You can now run the script (F5).

9. Now hopefully you will get the same error message again, but this time when you double-click on the error message you will get to the exact script line that is causing the error, so that you can correct it in the project. Also, you will probably get all of the PRINT output leading up to that, which may help.