SQLCMD and batch file using setvar

• Dean Jones-454305

  SSCarpal Tunnel

  Points: 4804

  More actions

  January 28, 2010 at 10:29 am

  #94421

  I wanted to know what i need to do in order to use a batch file for script deployment.

  Currently, the actualy sql files have the database names as variables, so I need my batch file to use sqlcmd to deploy which i can happily do if the sql files dont have variables.

  In management studio I can get this to work quite easily by doing the following.

  :setvar TargetDeployServer "SERVER1"

  :setvar master "master"

  :setvar FileToexecute "master.sql"

  :setvar workpath "C:\Deploy\"

  :r $(workpath)$(FileToExecute)

  now in a batch file, I need to be able to use setvar as the sql file looks like this:

  use [$(master)]

  GO

  select name from sysobjects

  my batch file looks like this

  :setvar TargetDeployServer "SERVER1"

  :setvar master "master"

  :setvar FileToexecute "master.sql"

  :setvar workpath "C:\Deploy\"

  :r $(workpath)$(FileToExecute)

  for /F "delims=/" %%X in ('dir /b /ON *.sql') do SQLCMD -S %ServerName% -d %DBName% -b -E -I -i "%%X" > "%%X.txt" 2>&1 || exit /b

  [/CODE]

  This will effectively install all .sql files in a directory.

  For some reason, when it gets to that file, it cannot understand the $master variable.

  Is there anything I can do to resolve or is powershell better ?