SQLCMD

:SETVAR <variable name> ["<variable value>"]

The SETVAR option, already mentioned earlier during the discussion on scripting variables, enables you to set their values programmatically inside the script.

One interesting aspect of the SETVAR command is that it is possible to assign a value that consists of multiple words. In this case, the variable value should be enclosed into the double quotes characters.

:setvar querytext “select * from pubs..authors”
$(querytext)

The query above results in “select * from pubs..authors” being submitted to the server.

It is possible to define a variable that spans multiple lines, as long as it is enclosed in quotes and can be executed as one batch.

Try executing

:setvar querytext “select au_lname from pubs..authors
where au_id = ’172-32-1176′
”
$(querytext)

You’ll see the following result:

au_lname
————————————
White
(1 row(s) affected)

In order to undefine a previously defined variable, use the SETVAR syntax without specifying the variable value:

:setvar <variable name>

It undefines the previously defined variable. For example,

:setvar ProcName sp_who
exec $(ProcName)
go
:setvar ProcName
exec $(ProcName)
go

results in the following error during execution of the second exec statement: “A fatal scripting error occurred. Variable ProcName is not defined.”

[:]!! <command>

Typing !! allows the user to pass essentially any operating system command straight to the command line interpreter for direct execution and passes the output back to your display.

Warning

This is a very powerful option that can allow a malicious script writer to break through your security. If you are not sure of the contents of the script, you should consider using the -X command line option while executing it.

Support of the !! command and its implementation makes interesting scenarios possible while inside Query Editor that have never been possible before. For example, you can see a list of all files in your Program Files folder on your local machine inside the Messages tab if you do the following while in SQLCMD mode:

!!dir “%programfiles%”

Note that there is no dir.exe command, but it is available while inside the cmd.exe command interpretor, so the command works successfully; it gets expanded to something like this (depending on location of your Windows directory).

“C:\WINDOWS\system32\cmd.exe /C dir”

In general, any command is passed as a parameter to the Windows cmd.exe shell.

As with other SQLCMD commands, !! supports specifying a SQLCMD variable as its parameter.

Consider an example of backing up the Pubs database to a share. Remember that the database is on the server to which your query window is connected, but the command specified to the !! command is executed on your local computer on which you run SQL Server Management Studio. For the purpose of this example, imagine that your file server name is “myserver” and the share name that you (and the account under which SQL Server service runs) can access is called “backups.”

:setvar backupDir “\\myserver\backups\pubs”
!!if not exist $(backupDir) md $(backupDir)
GO
BACKUP DATABASE [pubs] TO
DISK = N’$(backupDir)\pubs.bak’ WITH NOFORMAT, NOINIT,
NAME = N’pubs-Full Database Backup’, SKIP, NOREWIND,
NOUNLOAD,  STATS = 10
GO

In this query, SQLCMD variables are used to share context between the !! command and the SQL Query. The first line introduces a new SQLCMD variable called backDir and initializes it with the file share. The second line uses the command interpretor’s language to ensure that the directory exists. After that, the directory name is passed in to the T-SQL backup database statement to back up the database to the specified share.

Tips and Tricks

If you try executing this example and you receive an Access Denied message output to your Messages tab, it probably means that either you or the user under which SQL Server runs do not have write permission for the share.

This example uses command shell language directly inside a query window. Usually it is preferable to encapsulate it into a batch file and then call the file from the !! command. Let’s rework this query to use the batch file to create a directory for the BAK file.

Open your favorite text editor and create a c:\batches\prepare.bat that contains the following statement:

if not exist “%1″ md “%1″

This means that it checks to see whether the directory specified as the first parameter to the batch file exists, and if it does not, then it creates it.

After that, adjust the query like this:

:setvar batchFile c:\batches\prepare.bat
:setvar backupDir “\\myserver\backups\pubs”
!!$(batchFile) $(backupDir)
GO
BACKUP DATABASE [pubs] TO
DISK = N’$(backupDir)\pubs.bak’ WITH NOFORMAT, NOINIT,
NAME = N’pubs-Full Database Backup’, SKIP,
NOREWIND, NOUNLOAD,  STATS = 10
GO

It does exactly the same thing as the previous query, only this time it calls the batch file and specifies the backupDir SQLCMD variable as the parameter.