SQLCMD supports parameterized variables and provides ":" commands

SQLCMD is a new command line tool provided by SQL 2005, which depricates the old ODBC based OSQL. The functionality of SQLCMD has been greatly enhanced beyond OSQL.

SQLCMD can be useful for running some SQL scripts which require you to pass different parameters specific to a certain environment. For example, you can use it in a WiX customization (CustomAction) to set up you database.

The following is an example: Use notepad to create a SQL script as: sqlcmd.sql

    DECLARE @myVariable varchar(255)
    SET @myVariable = '$(myVariable)'
    SET @myVariable = @myVariable + ' ' + suser_sname()

    PRINT @myVariable

You can run it from a command console:

    SQLCMD -b -w4000 -l10 -E -i"sqlcmd.sql" -v myVariable="hello"

SQLCMD provides ":" commands

SQLCMD supports ":" commands, which provides you some kind of micro functionalities. Following is a list of some interesting commands.

1. :ed to edit the current batch with the editor specified in the sqlcmdeditor environment variable. SET SQLCMDEDITOR=notepad
2. :!! to run a cmd.exe command. For example: !!dir
3. :r to read a file in as a sqlcmd script
4. :setvar <name> ["value"] to set a sqlcmd variable that can be accessed with $(name). Also environment variables can be accessed with the same syntax.
5. {:error|:out|:perftrace} {|stderr|stdout} to redirect the relevant output streams
6. :xml {on|off} to control outputting data in the result set style or as XML.
7. -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.

See also

THIS POST IS PROVIDED "AS-IS" WITH NO WARRANTIES AND CONFERS NO RIGHTS. Build time: Sun 03/30/2008 . ©2007 Dalun Software. All rights reserved. Back to Article List