Bubbling up SQL Script errors within Jenkins CI Server using sqlcmd -b argument

We use Jenkins Continuous Integration server at work and frankly its so dug into our work pipeline, its hard to remember how we worked without Jenkins.  I bet a lot of people say that once they become addicted to Jenkins.

One thing I wanted to do recently was: Schedule a SQL Server script to run against our dev server that would test one of our key functions (it not central to the point here but the function takes numbers and rounds them to various rounding rules).  This thing really needs to return correct numbers consistently and so we have hundreds of assertions to make sure its working.  To assert pass or fail, the end of the script selects against rows in a seed table and returns back any failed assertions.

Pseudo code:

select * from dbo.rounding_test where expected_val <> rounded_val;

Previously a human would run this script manually during our release procedures and if no rows were returned it passes, rows returned a bug is logged.  Obviously this is a perfect scenario to automate.

Problems: I could not get Jenkins to detect when a script had SQL errors and I didn't know how to inform Jenkins that there were rows in that assertion table which would indicate the function was suddenly failing testing.

There are many ways one could do this but there are some cool nuggets in here, so here'a what we did.

First I decided that I would raise a true SQL error in my SQL Server test script when rows existed in my assertion table (this indicates a fail of the test).  To do this the end of my script already has this for human eyes:

select * from dbo.rounding_test where expected_val <> rounded_val;

But I added this for Jenkins:

if exists (select * from dbo.rounding_test where expected_val <> rounded_val)
 select 1/0; -- this is designed to intentionally raise an error in Jenkins
go

So when a human runs this and its failing you will see rows and a big ugly divide by zero error.

Next, let's automate this in Jenkins so I don't have to remember to do this manually... I'll schedule it to run periodically in Jenkins and have Jenkins send me an email when it fails.

In Jenkins I create a Freestyle project and add a build step "Execute Windows Batch Command".  The command I add will be:

sqlcmd -S localhost -d mydatabase -U user -P pw -i "C:\SQL Server_tests\roundingrules.sql"

Save and run the Jenkins job.  It passes.

Now, edit rounding rules in a way so that it intentionally fails one or more of the tests (I just put a bogus value into expected_val seed data).  Then save and run the Jenkins job.  It passes.  Examine the console log and review it for errors.  Indeed when I see the console log it references a divide by zero error there but Jenkins reports SUCCESS.  To be sure edit the script again and change the table name to something that doesn't exists, such as:

... select * from dbo.FRED

Save the script and re-run the job.  The script will raise an error but Jenkins will still pass.

This is because sqlcmd is not being run so that errors bubble up.  To do this you want to change your command in Jenkins to use the -b argument and also detect the error and echo it up in your Jenkins Windows Batch Command as follows:

sqlcmd -b -S localhost -d mydatabase -U user -P pw -i "C:\SQL Server_tests\roundingrules.sql"

IF %ERRORLEVEL% NEQ 0 ECHO "Error"

Save the Jenkins job and run it.  Now, I see Jenkins is failing the job and I get an email about the build being broken.  Awesome.

Next, I correct my sql script (change the table name back to the correct one, and correct the intentional rounding failure in expected_val) and re-run the Jenkins job.  It passes.  I get an email from Jenkins saying the build is back to normal.

Now I can rely on this script running faithfully in Jenkins to inform me when this key area of our app is broken by someone, and I don't have to remember anything.  We will build upon this approach and start automating the testing of other areas as well.

Next, I have to figure out how to do the same with Oracle's sqlplus because we support Oracle as well and thus we need the same test coverage on Oracle as we do on SQL Server.