Using SQLCMD with SQL Server 2005

You can’t always use a GUI (examples are batch scripts, DAC, etc.) and so MS provides sqlcmd. This utility replaces the older ‘osql’ utility which won’t be supported in future releases. With sqlcmd you have a host of command line parameters to allow for a line by line interface with the database engine. You can find sqlcmd in this directory for SQL Server 2005:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\

Using with Variables
One of the more powerful features is the ability to run in ‘variable mode’ so you can make simple (or complex) boilerplate programs. An example. You are always checking the last rows of particular tables but you want to be able to automate it. Using the sql file ‘latest.sql’ with this content:

select * from $(tablename) order by $(primarykey) desc

you could do this with tblmytable (which has the primary key mytableid_pk) using this on the command line:

sqlcmd -i latest.sql -v primarykey="mytableid_pk" tablename="tblmytable" -o latest.output

The table contents would be contained in reverse primary key order in the file ‘latest.output’.

DAC is something different and more important for administrators. It is a Dedicated Administrative Connection and is accessed using:

sqlcmd -A

This is a very special feature as it allows access even when the server hangs or is otherwise unavailable. It has its own private SQL Server scheduler and so the only way it can be excluded from access is if the SQL Server service has been stopped or paused. You could go into an unresponsive server and attempt to diagnose the problem, end connections or just shut down properly:

sqlcmd -A
1> shutdown with nowait
2> go


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s