T-SQL Command Line Options – Part 2 – SQLCMD.exe

SQLCMD.exe

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.

SQLCMD-help

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.

SQLCMD-windows-auth-connection

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.

SQLCMD-select-with-union

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.

SQLCMD-Q-parameter

What’s next?

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.

Navigation

  1. Part 1 – Introduction
  2. Part 2 – SQLCMD.exe using command prompt
  3. Part 3 – .NET objects using Powershell
  4. Part 4 – SQLPSX using Powershell (SQL Server 2000 and above)
  5. Part 5 – SQLPS using Powershell (SQL Server 2012 / 2014)

This article has 1 comment

Leave a Reply

%d bloggers like this: