Support Center

Working with SQLCMD Variables

Last Updated: May 01, 2014 09:19PM AEST

This article has moved to a new home. For up-to-date documentation please continue reading at http://doc.ready-roll.com/display/RRSQLDOC/SQLCMD+Variables


If in your organisation you need handle subtle differences in the setup of your environments (eg. Test/Staging/Production), or if you are deploying to customer sites with variations that you don't want to hardcode into your project scripts, it can be useful to provide certain values from an external source. ReadyRoll 1.4 allows you to do this by tapping into the SQLCMD variable support built-in to Visual Studio, providing you with a way to write scripts that use different values depending on the context.
 

When working within Visual Studio, you can use sandbox or project-level, and outside the IDE you can provide environment-level values from an external source, like a Continuous Integration/Automated Deployment server (eg. Octopus).

 

Project and Sandbox-specific values

Start by adding a variable to the SQLCMD Variables tab in the Project Property pages. The value you provide in the Default column will be stored in the project file (and therefore shared with other team members) however the Local value is specific to your machine (stored in the non-source controlled .user file). If you leave the Local column blank, the Default will be used when deploying inside Visual Studio.

Screenshot - 6_12_2012 , 9_05_51 PM


Using the $(VariableName) notation, reference the variable you just created in a new Deploy-Once script.

Screenshot - 6_12_2012 , 9_08_50 PM


When you build the project, the variable will be substituted with the Default value (or Local value, if it was provided).

Screenshot - 6_12_2012 , 9_12_19 PM



Environment-specific values

When deploying your database outside of Visual Studio, you can also provide a set of values for the variables defined in your project.

To do this, first enable SQLCMD package scripts for output (or enable .nupkg output if you are using Octopus). Build your project to produce the package deployment script, eg. MyDatabase_Package.sql. Notice in the header of the file that the full list of project variables are included, but commented out along with their default values. You will need to provide values for each of the variables listed, as part of the next step to deploy your database (Note that $(DatabaseName) is a built-in SQLCMD variable that is required for all database projects).

To deploy your database, open the command prompt and execute the following command:
 

SQLCMD.EXE -E -S "(localdb)\Projects" 
           -i "AdventureWorks_Package.sql" 
           -v DatabaseName=AdventureWorks_STAGING 
           -v Environment=STAGING

Screenshot - 6_12_2012 , 10_03_07 PM

See MSDN for a full list of SqlCmd.exe switches.

Note: There is currently no straightforward way of passing SQLCMD variables into MSBuild; at present the Package deployment method is the best way to use SQLCMD variables at this time. If you'd prefer to use the Patch deployment script method instead (which provides a delta file of pending migrations and is only available via MSBuild), please get in touch.

 

 

Further reading:

readyroll.support@red-gate.com
http://assets2.desk.com/
false
readyrollsql
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete