SQL Server 2005/2008 provides a command line utility SQLCMD, which can be used to perform basic SQL operations using command prompt. This comes really handy when automating repeatedly used SQL operations. You don’t need to Open the Management Studio every time to run a query or backup/restore a Database. All this can be scripted using SQLCMD in a batch file and run from the command prompt.
Need arises at times to pass the parameters to SQL scripts at runtime. This post described the way to achieve this.
In this example, we are discussing a particular scenario, where we need to connect to a remote SQL Server instance, read contents of a table in a database, and store the query results in a text file.
The following parameters will be passed at runtime:
- SQL Server instance name
- Database name
- Table name
- Output file location
We will first have a look at the sql file
|use $(dbname) |
select * from $(tablename)
It is a simple SQL file which selects the content of a table and displays it. The only unconventional thing you may notice is the use of $(dbname) and $(tablename) instead of the actual DBname and table name. This is because, these are variables, values for which will be passed while calling the sql script.
Now, lets have a look at the batch file which calls this SQL script.
|sqlcmd -S %1 -i select.sql -v dbname = %2 tablename = %3 -o %4|
We will call this batch file something like this:
|CallSqlScript.cmd SqlServer01 EmployeeDb EmpContactTbl E:\SQLLogs\EmployeeContact.txt|
Notice the 4 parameters we are passing to the batch file
This batch file is invoking the SQL Command line utility SQLCMD, the switch –S is used to specify the SQL Server name/instance which should be contacted. The value of this is set to %1 which means the first parameter passed to the batch file, SQLServer01 in our case.
-i switch specifies the input SQL file, which will be executed, select.sql in our case.
-v switch gives us an option of passing parameters to the SQL file select.sql.
We are passing 2 parameters to the SQL file, dbname and tablename, values of which are set to the 2nd and 3rd parameter being passed to the batch file respectively.
the last switch is the –o switch, which will save the output of the sql query to a plain text file. If we don’t use the –o switch, the output will be displayed on the screen.