Using System Environment variables in sql script

Question

In SQL script

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

Will the script display% DataDrive% from environment variables? If not, is there a way to go to the DataDrive environment variable from the SQL script?

Answer 1

SQL server sqlcmd supports script variables that can be set in one of three ways:

• Locally in a script using :setvar , as you do above
• Passed script using the -v option
• Set as environment variables before running the script

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