Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

When was the last time you used sqlcmd.exe?

With the growing popularity of windows server in CORE mode the command line is officially back in vogue.  PowerShell is getting all the press but there is a lot more to the command line than just PoSh.  Long before server CORE and PoSh were grabbing headlines and blog titles – savvy DBAs were interacting with the SQL Engine at the command line with sqlcmd.

There are a variety of reasons you might do this even in a normal GUI environment.  For a quick query it’s much faster and way less overhead than opening up the full blown SQL Management Studio.  Further, if your system is near un-responsive and you need to use the dedicated administrative connection to access the server, sqlcmd might be one of the only ways you can get a response back.  John Miner pointed out to me on twitter that sqlcmd with the windows task scheduler is a great way to get around the absence of SQL Agent in SQL Express.  Finally – it’s just cool.  And the ABCs of database administration requires us to A-lways B-e C-ool.

The actual sqlcmd utility is found in:  c:\program files\Microsoft SQL Server\<version no>\tools\binn

In a typical installation however you can just run it from the command prompt without specifying the actual path.  Once you’re connected to the server working with sqlcmd is as easy as issuing t-sql statements followed by the GO keyword to execute.

Here are a few tips for using sqlcmd effectively.

Know the basic options and behaviors for connecting

I prefer windows authentication mode on my servers so more often than not I simply type sqlcmd at the prompt and hop right in.  No additional switches or options are needed.  Issuing sqlcmd without additional options will connect to the default instance on the current box with integrated security (i.e. current credentials).

Often however you’ll need to specify a specific server\instance, or use a sql account so you should also know these three switches off hand.

-S  specify server\instance
-U  specify a user name
-P  specify a password

If you specify -U but not -P it will prompt you for the password – this avoids people shoulder surfing your sensitive prod passwords.

A fourth that is also good to know

-d  allows you to specify the database to use – but you can also just issue a USE statement to get what you want once connected.

Another switch that every DBA should know by heart is -A.  This is the switch that instructs sqlcmd to use the dedicated admin connection.  Usually if you need this switch, you really need it.  You don’t want to have to be googling around for it while production is in the throws of chaos.

There are a lot of additional command switches and options but I will refer you to books online – no need to rehash them here.

Keep it simple and concise

There isn’t a lot of room in the cmd window for large outputs.  Running an sp_who, select * from sys.dm_exec_requests, or Machanic’s sp_whoisactive without reducing options will overwhelm the command window with data that isn’t aligned or easy to read.  For example a select from sys.dm_exec_requests looks like this in the command window of sqlcmd.  Not much you can do with these query results.

select_star

As an efficient DBA you should be running very specific queries anyway, but with sqlcmd it’s even more important.

Notice in the following example I break each clause onto it’s own line.  sqlcmd won’t run the query until you issue a go command so you can use as many lines as you need to make it clear.  Doing so makes it easy for me to review the script before I issue the GO.  Second, I’m selecting a single value from a single column.  So it’s very easy to read the results when they do come back.

select

If you can’t be concise use an output file

Ok fine, there is a fifth command line option you should know.  When you start up sqlcmd you can specify -o followed by a path to an output file.  So, if you really do want to run sp_whoisactive or exec sp_who and capture all the results.  Plan ahead and output everything to a file.

output

Notice that I issued two command above.  I switched my database to adventureworks2012 with a use statement and then I ran the sp_who stored procedure.  Neither command produced any output visible to the screen.  It instead wrote out the results to the output file I designated in my sqlcmd session ‘queryresult.txt’.

View output files with notepad (yes, even in core)

Something you may not know… windows server core still supports notepad.  It’s a watered down version but gets the job done.  So from the command line (even in core) you can now view the results opening the output file with notepad.

notepad

notepad_show

Notepad gives you scroll options and uses a fixed width font – so everything will line up like you are used to in the SSMS window.  You’ll notice that the output file captured the output’s of both batches.  You’re not limited to a single query with an output file.

Running a script in a single command

Oh, wait – there is a sixth you should really know.  But that is the last one – promise.  You should know that -i followed by a path to a sql script file will run the content of that script.  The i stands for input file.  Be careful, there are no “are you sure?” prompts or anything.  Once you hit enter it will run.  But if you have a script prepared in a file ready to go… this is a whole lot easier than typing the whole thing out.  This is also how you would use sqlcmd in conjunction with the windows task scheduler.

dash_I

Interesting side note on sqlcmd script files.  If you want to get fancy with scripts know that sqlcmd isn’t just limited to TSql.  It has a complete set of additional syntax elements unique to the utility.  These allow you to declare variables, run external command line operations, re-direct output to files, and even switch between database servers in a single script file.  The same msdn documentation I keep referring you to has all the details.  Also check sample usage and enabling support for this additional syntax in SSMS via sqlcmd mode.


There you have it.  sqlcmd and the six command line options that you should know by heart.  Uhh, seven if you count -A.

I challenge you to use sqlcmd the next time you need a quick answer from the server.  I would also challenge you to practice using sqlcmd with the -A option to get used to working with the dedicated admin account and look into reasons why you might need that account.  Like I said above, when you need that one – you need it now.

2 comments on “When was the last time you used sqlcmd.exe?

  1. Pingback: (SFTW) SQL Server Links 28/02/14 - SQL Server - SQL Server - Toad World

  2. Pingback: (SFTW) SQL Server Links 28/02/14 • John Sansom

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on February 25, 2014 by in Career Skills.
%d bloggers like this: