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'doesNotExist@scm.com',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 🙂