Sqlcmd's cool new facilities

  • Article

Sqlcmd is the SQL Server 2005 replacement for osql which in turn is being deprecated in SQL Server 2005. An irritating aspect of osql is that it lacks basic macro facilities. A macro processor like M4 could be used to preprocess the script but that required deploying M4. So I was excited when I learnt that sqlcmd had macro like features built-in and I could more often avoid writing code like:

declare @attributesSchema as xml

set @attributesSchema = (select * from openrowset(bulk 'C:\Source\Program Database\Attributes.xsd', single_nclob) as [file])

create xml schema collection [Program].[Attributes] as @attributesSchema

Sqlcmd adds support for :commands which give you much of the functionality of a macro language. Some of the most interesting :commands are:

  • :ed to edit the current batch with the editor specified in the sqlcmdeditor environment variable

  • :!! <command> to run a cmd.exe command

  • :r <path> to read a file in as a sqlcmd script

  • :setvar <name> [“value”] to set a sqlcmd variable that can be accessed with $(name). Also environment variables can be accessed with the same syntax.

  • {:error|:out|:perftrace} {<path>|stderr|stdout} to redirect the relevant output streams

  • :xml {on|off} to control outputting data in the result set style or as XML.

There is a sqlcmd mode in Management Studio that works with all these :commands except for :perftrace. There are several other interesting commands listed in the documentation and an alphabet soup of command options.

A nice new command option is:

  • -X [1] stops security sensitive commands from running (the 1 flag causes the script to terminate instead of just issue a warning). The flag can help avoid client side scripting style attacks.

Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm

This posting is provided "AS IS" with no warranties, and confers no rights.