Run Sql Query from Jenkins or any CI Tool

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 :

cd "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"

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

SQLCMD -S <YourSQLServerDBName> -U <YourSQLServerUsername> -P <YourSQLServerPassword> -W -Q "SELECT * from <yourSQLServerQueryHere>"

If your Jenkins job failed :

In 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

BEGIN TRANSACTION;

/****** Script to create SQL User id for New VM - Ravi  ******/

use mydb

DECLARE @Now DATETIME

DECLARE @EndOfTime DATETIME

SELECT @Now = GETDATE()

SELECT @EndOfTime=CAST('31-DEC-9999 00:00:00.000' AS DATETIME)

/**** Inserting the UserID *****/

INSERT [dbo].[T_UserProfile]([UserName], [StoreId], [EmailId], [IsActive], [CreatededDate], [CreatedBy], [LastModifiedDate], [LastModifedBy]) VALUES ('%VMNAME%\win-admin',NULL, N'[email protected]',1,@Now,N'Test Data',NULL, NULL)

/**** Inserting the RoleID for the new User *****/

INSERT INTO [dbo].[T_UsersInRoles]([RoleId], [UserId], [CreatedBy], [CreatedDate], [ModifiedBy], [ModifiedDate]) SELECT 1, (SELECT TOP 1 UserID FROM [dbo].[T_UserProfile] ORDER BY [UserId] DESC),'Test Data', @Now, 'Test Data',@Now

ROLLBACK TRANSACTION;

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

If you are getting Error like below :

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn>sqlcmd -S mydbdom.database.windows.net -U sqladmin -P ******** -W -Q "SELECT * FROM [dbo].[T_UserProfile]"

Msg 208, Level 16, State 1, Server bootspoc, Line 1

Invalid object name 'dbo.T_UserProfile'.

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

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn>sqlcmd -S tcp:mydbdom.database.windows.net,1433 -U sqladmin -P ******** -W -Q "SELECT * FROM [mydb].[dbo].[T_UserProfile]"

Msg 40515, Level 15, State 1, Server bootspoc, Line 16

Reference to database and/or server name in 'mydb.dbo.T_UserProfile' is not supported in this version of SQL Server.

Then how to resolve this ?

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

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn>sqlcmd -S tcp:mydbdom.database.windows.net,1433 -U sqladmin -P ******** -d mydb -W -Q "SELECT FROM [mydb].[dbo].[T_UserProfile]"

(17 rows affected)

Error resolved 🙂

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

You have successfully ran the SQL query through Jenkins 🙂