Using System Environment variables in a sql script

Question

In a SQL script

GO
:setvar DefaultDataPath "%DataDrive%:\SQL\MSSQL\Data"

Will the script pick up %DataDrive% from the environment variables? If not, is there a way to get to the DataDrive environment variable from the SQL script?

Answer 1

SQL server's sqlcmd supports script variables, which can be set in one of three ways:

• Locally in the script using :setvar as you do above
• Passed to the script using the -v option
• Set as environment variables before the script is run

In other words, you can replace %DataDrive& with $(DataDrive) in your script and either set DataDrive as an environment variable before running, or pass e.g. -v DataDrive=D:\.to your script when running with sqlcmd.