Parse a SQL Script Before Execution

Recently, we’ve added support for running database scripts as a part of our deployment pipeline. One of the steps added to the pipeline was a check to ensure the sql script that would be executed parses correctly. This is a useful evaluation far before a deployment, not just to prevent problems, but to give fast feedback to our teams so they can correct the issue.

The solution turned out to be much more generic than we had imagined. Because we didn’t yet know how generic the solution was we built some custom code for executing sql commands against a database. However, if we were to go back and do it a second time, we would probably use an open source tool to perform the execution of the scripts:

• dbatools
  
  This is an amazing powershell module of database related commands and tools. It is designed to be used in DevOps pipelines and to make DBA’s lives easier.
  
  We looked at this module when first implementing the parsing functionality, but we wanted to get the output (including normal vs error output) in a particular way and we would need more time to get it to fit our exact needs.
  
  But! It’s an absolutely amazing collection of tools and we will definitely revisit it.
  
• Invoke-SqlCmd2 (script)
  
  This script/module is a healthier and more secure version of Invoke-SqlCmd. It adds in functionality that makes your life easier, without adding on soo much that it becomes confusing to use.
  
  We started out using this, but then had some extra needs for error handling and output parsing that forced us to explore alternatives.
  
• sqlcmd.exe
  
  This is what we ended up using. It’s a command line utility so it’s doesn’t have native powershell output. But, the output feels a lot like the output from SQL Server Management Studio. Because of that comfort, it made it the easiest to wrap our heads around for handling error and output parsing.

The Parsing Script

For Microsoft SQL Server, parsing is made possible by wrapping your SQL query in the set parseonly directive.

From there, you just need to send the script over to the SQL Server and execute it. If SQL Server sends back no output, then it parsed successfully. It’s nice and straight forward.

Lessons Learned

QUOTED_IDENTIFIERS

SQL Server Management Studio has a number of default settings which you are generally unaware of. One of those is the directive QUOTED_IDENTIFIER is set to ON. When using the sqlcmd.exe utility, you will need to use the –I parameter in order to turn on the same functionality.

The Invoke-DbUpdateScript.ps1 below uses the parameter.

The error message looks like this:

Msg 1934, Level 16, State 1, Server xxxxxx, Line 14

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Linked SQL Servers & Transactions

We required that all queries which are run through the deployment pipeline must be wrapped in a transaction. This guarantees that if any error occurs (which will end the connection to the server) will be rolled back automatically. However, this could result an error message of: “Could not enlist in a distributed transaction.”

To work around the problem change the transaction isolation level from SERIALIZABLE to READ COMMITTED.

Could not enlist in a distributed transaction.

Scripts