Topic: sqlcmd variable from a batch file to a sql script
Scripting is a powerful way to automate your systems. It should come as no surprise to discover that SQL Server provides a way for developers to send in parameters to a sql script using a batch file via the sqlcmd utility (for SQL Server 2005 and later). sqlcmd is a very versitile utility and I’ll be covering more of its uses in other posts but we’re focusing on sending a variable (possibly more than one) to a sql script either in a batch file or straight from the command line. So without further ado, let’s take a quick look at the syntax…
sqlcmd -v <name of variable>=<value> -v <name of variable>=<value>
This first example is showing the basic idea around passing multiple variables in on the command line using the -v switch followed by the variable name equals value. To be practical, you might need to use a few other switches in order to tell sqlcmd the server you want to connect to ‘-S’ followed by the user (-U) and password (-P). The ‘-S’ is required for SQL Express and the ‘-U’ and ‘-P’ might be required if you’re using SQL Server authentication. Last but not least is the ‘-i’ which tells sqlcmd that there are input file(s) to follow. Yes, you can have more than one of these as well and if you list more than one file they’ll be executed in order.
sqlcmd -S <sql server instance name> -U <username> -P <password> -v <name of variable>=<value> -i <your script.sql>[,file2...]
To really bring this home to roost we also need to know how to access the variable once it has been passed in to our sql script. This is done by using the following…
So, now we’ve defined all the essentials let’s put it all together to make something that actually works.
First, we’ll create an extremely simple sql script named script.sql and place this code in it…
Next, lets create a file named batchfile.bat and place the following inside of it…
sqlcmd -S EXAMPLESQL-COM-PC\SQLEXPRESS -v path="'C:\test\test'" -i script.sql,script.sql
From the command line (assuming both files are in the same place) we execute the batch file…
------------ C:\test\test (1 rows affected) ------------ C:\test\test (1 rows affected)
A couple of final notes. I passed the same script file in twice just to show you what that look like. There shouldn’t be any spaces between the files. Also, the dealing with paths specifically, you’ll need to make sure you quote them properly like I did above or else they won’t work properly. You’ll need to change the -S string to your sql server instance name if you want to give this a try.
Valid for SQL Server 2005, 2008, R2, 2012, 2014.