In SQL Server 2005 and above SQLCMD.exe comes packaged with each installation regardless of edition. This part of the T-SQL Command Line Options blog series will cover the basics for executing T-SQL queries and commands using SQLCMD with command prompt. In SQL Server 2000 and below there were other utilities, osl and isql, which I will not be covering. Since osql is still available in versions above 2000, I urge you to take a moment and read Pinal Dave’s post, SQL Server SQLCMD vs. osql basic comparison.
How to use it
SQLCMD.exe is in this file path: C:\Program Files\Microsoft SQL Server\\Tools\Binn. Reference: MSDN.
Our first example of how to use SQLCMD.exe is to open up a connection with the server and then write one or more T-SQL batches directly in the command shell without closing the connection. For these we will use the -S parameter for the server name (see above for all options) and -E to use Windows Authentication; alternately -U and -P can be used for SQL Authentication.
Once this connection is established you will see a 1> displayed and your cursor will have moved down to the new line. From here you can write your T-SQL command. Pressing enter will move the cursor to the next line but will not attempt to execute yet. Once you have completed your statement you enter a GO to complete the batch and then press enter. This will cause the entire batch to execute.
The next example is useful when you’d like to execute a pre-made statement programmatically. Here we will use the -q and -Q parameters to pass in a query without the interactive prompting. -q is used to execute a query and leave the connection open while -Q will execute and close the connection. There is also the -i parameter which is used to pass a file path of a T-SQL file for execution. Below is an example of the -Q parameter.
Make sure to check back in next time for part 3 of T-SQL Command Line Options where I will be covering the .NET objects required to establish SQL Server connections. Powershell will be the selected command shell for part 3.
- Part 1 – Introduction
- Part 2 – SQLCMD.exe using command prompt
- Part 3 – .NET objects using Powershell
- Part 4 – SQLPSX using Powershell (SQL Server 2000 and above)
- Part 5 – SQLPS using Powershell (SQL Server 2012 / 2014)