This post announces the availability of a small helper utility that will allow you to maintain your database.sqlcmdvars files from a command line tool.

Description:

What is the scenario? If you are using vsdbcmd.exe to deploy your schema, you include the Database.sqlcmdvars, however vsdbcmd.exe does not have an option to override the values of the SQLCMD variables used at the command line level like you can do when using MSBuild (assuming you chained the SQLCMD variable to a MSBuild property).

This little tool, named SqlCmdVars.exe, lets you update you Database.sqlcmdvars file from the command line, dump its content, query the existence of variables, delete an existing variable, update the value of an existing variable and add new variable with value if required.

Usage:

The tool has a couple command line switches, if you no longer know you can always do SqlCmdVars.exe –? and it will provide you the list of command line parameters available. You can interchange the forward slash and dash as the parameter prefix, so /? and –? have the same effect.

Parameter Short form parameter Description
/Input:<string> /i:<string> Defines the input file, this is the only mandatory parameter. You provide an absolute or relative file path to the input file. You have to include the full file name, so name plus file extension.

/Input:d:\demo\dbproj\Northwind\Properties\database.sqlcmdvars

/i:..\dbproj\Northwind\Properties\database.sqlcmdvars

If there are spaces in the path, you place double quotes around the file path and name like this:

/i:”d:\users\gertd\Documents\Visual Studio 2008\Projectsj\Northwind\Properties\database.sqlcmdvars”

NOTE: If you are updating the content of the sqlcmdvars file which you are pointing to, using the /Property or /Delete command and you did not specify a explicit output file using the /Output command, you are implicitly updating the input file!
/Output:<string> /o:<string> Defines the output file. If the output file already exists, it will be overwritten. You can provide an absolute or relative file path to the output file.

NOTE: You have to make sure you can write to the file and directory location you specified.
/Property:<string> /p:<string> The /Property command adds or updates a variable name-value pair. The syntax is equivalent to the MSBuild task properties

/Property:<variable name>=<property value>

For example when you want to add or update a variable named DefaultLogPath you use this:

/p:DefaultLogPath=”D:\dbs\MSSQL10.MSSQLSERVER\MSSQL\DATA”

If you want to delete a variable you can do this by assigning an empty value like this

/p:DefaultLogPath=

You can have multiple /Property commands on a single command line invocation, but the command must be unique. This means you can provide values for the same variable multiple times like this:

/p:prop1=value1 /p:prop2=newValue2 /p:prop1= /p:prop1=value2

The final result of this is that variable named prop1 will have the value2, the last assignment wins.

NOTE: In order to delete a variable you must include the equal sign (=), omitting the = sign will result in an error.

NOTE: Variable names are matched case-insensitively, the casing of variable values is preserved.

NOTE: Each /Property command must be unique, for example the following will generate an error “Duplicate 'Property' argument 'prop1=value1'”

/p:prop1=value1 /p:prop1= /p:prop1=value1
/Delete:<string> /d:<string> The /Delete command allows you to explicitly delete a variable given a variable name. For example to delete a variable with name prop1 you write:

/Delete:prop1

or

/d:prop1

You can have multiple /Delete commands on a single command line invocation. However the commands must be unique.

NOTE: Variable names are matched case-insensitive.
/Query:<string> /q:<string> The /Query command lets you search the input file for a given variable name and when found it will return its current value.

For example:

/Query:DefaultLogPath

or

-q:DefaultLogPath

Will return the value: D:\dbs\MSSQL10.MSSQLSERVER\MSSQL\DATA

The /Query command also sets the exitcode when a property is found, so it can be used inside a batch file using ERRORLEVEL to determine if a property is defined or not.

@echo off
SqlCmdVars.exe %*
IF ERRORLEVEL 2 @echo NOT FOUND
IF ERRORLEVEL 1 @echo FOUND

The default ERRORLEVEL for other operations is 0 (zero), errors are indicated through error levels 99 and up.
/List[+|-] /l[+|-] The /List command dumps all name value pairs of all the variables defined inside the file.

The output looks like this:

BuildVersion = 2009.07.31.01
DefaultLogPath = d:\dbs\MSSQL10.MSSQLSERVER\MSSQL\DATA
@<file> N/A You can use a response file to make it easier to handle multiple or large parameters. The response file contains the parameters in the format described above.
/Help[+|-] /? Show the command line help information

 

Download:

You can download SqlCmdVars.zip from the DBProj.com website.