SqlCmd variables appear to be object references or at least something related, not strings.

SqlCmd variables can be set to strings, but they can also be set to things like a database name, path and file.

I do not understand exactly how the SqlCmd processor is working, but here are some ways you can use SqlCmd variables, at least this works on my Windows 7 PC with Sql Server Express and SMSS 2008 R2.

Strangely, this works.

:setvar v1 mydatabase
use $(v1);
print 'database name=$(v1)'

You can set a variable to a database name and then use the database name in a USE statement. To print the database name, the SqlCmd variable needs to be inside the quotes.

If the SqlCmd variable is a string, then it does not need to be inside the quotes. This works.

:setvar v1 'mydatabase'
print 'database name=' + $(v1)

But then the following USE statement would not work, seemingly because the variable is set to a string and not the database object reference.

:setvar v1 'mydatabase'
use $(v1)

The :r command can also use SqlCmd variables in the same way. This works.

:setvar v1 path\
:setvar v2 file.sql
:r $(v1)$(v2)
print 'path and file=$(v1)$(v2)'

If the path and files are strings, then the :r command will not work. This does not work.

:setvar v1 'path\file.sql'
:r $(v1)

If you are passing the variables in from Powershell you have to set the variables for the object references without quotes. This works.

# Powershell script
$vars="v1=mydatabase", "v2=path\myscript.sql"
Invoke-SqlCmd ... -Variable $vars

With this SqlCmd script.

use ($v1)
go
:r $(v2)

The above script would not work if you had quoted the values for the variables in the Powershell script. This does not work with the above SqlCmd script, since the variable values are now strings.

# Powershell script
$vars="v1='mydatabase'", "v2='path\myscript.sql'"
Invoke-SqlCmd ... -Variable $vars

Strange, but good to know.

By Andrew Weitzen, Bronze Inc. (c) 2010-present

Bronze is the publisher of several online Internet journals including: InternetHandholding.com, DomainNames.gs, DotNetNuke.bz, Programmer.bz, Software.vg, WebHosting.vg