Run Sql Query from Jenkins or any CI Tool

Topic Tools

Sometimes, there is a need to run sql transactions during Building the code.

You might have searched for the plugins but that is not easily available, but you need to run sql query through your CI-CD tool.

Now, what to do ?

You can use SQLCMD for the same 🙂

I am going to demonstrate how to use SQLCMD to run any sql query through Jenkins (CI Tool) and provide resolutions for some errors which you may face while using this utility.

There are few Pre-requisites which you need to perform :

Download & Install Microsoft ODBC Driver 11 for SQL Server – Windows (download link) – Pre-reqisite for installing SQLCMD.exe

Download & Install Microsoft Command Line Utilities 11 for SQL Server (download link) – This gives you SQLCMD.exe

Once downloaded, configure the Jenkins job to run the Build command as below :

(need to provide full path where you have installed the SQLCMD )

 

If your Jenkins job failed :

SQLCMD-Jenkins-Issue-5In case you are getting any error – The best way to debug it through SQL Studio which will proves the query is correct but not working in Jenkins with SQLCMD

You can try with dry run:- To do that insert at top BEGIN TRANSACTION; and at end ROLLBACK TRANSACTION;

For example : I am trying to create New user id for new VM – The below query should just run and show the outcome without actually affecting the database

SQLCMD-Jenkins-Issue-1

If the query is working fine, it means your SQLCMD command is having an issue.

If you are getting Error like below :

SQLCMD-Jenkins-Issue-2

Even if you try to insert the db name with table name it will not work 

SQLCMD-Jenkins-Issue-3

Then how to resolve this ?

You need to use -d parameter in your command to specify the database name :

SQLCMD-Jenkins-Issue-6

Error resolved 🙂

You can now use the same command in your Jenkins Job and Jenkins will successfully run the SQL Query.

SQLCMD-Jenkins-Issue-7

You have successfully ran the SQL query through Jenkins 🙂

2 comments… add one

  • Pinoy

    great i found this blog 🙂

  • Melin

    thanks, it helps

Leave a Comment