Passing parameters to sqlcmd

• Roust_m

  SSCoach

  Points: 17410

  More actions

  November 1, 2011 at 7:22 pm

  #390739

  Hi,

  I am trying to configure a job to run the following:

  sqlcmd -E -S localhost -d MyDB -i "\\FileServer\Scripts\DBFolder\08_SPs 20XX-XX-XX.sql"

  I need to be able to pass a parameter to sqlcmd so the script name ends with YY-MM-DD.sql, where YY-MM-DD refers to yesterday's date.

  Any ideas?

  Thanks

• TZ.DBGeek

  SSC Enthusiast

  Points: 183

  More actions

  November 1, 2011 at 8:08 pm

  #1401957

  Have you tired the -v parameter in sqlcmd in combination with using another environment (like Powershell) to do the date calculation and set it to a variable to then pass into, and generate, your sqlcmd expression? Or have one job step use tsql to do your filename date string expression and output it to a file for sqlcmd to then read in from on a subsequent job step?

  Let me know if either approach sounds reasonable and I can offer some help with syntax if desired. Thanks,

  --tz

• Roust_m

  SSCoach

  Points: 17410

  More actions

  November 1, 2011 at 8:12 pm

  #1401958

  TZ.DBGeek (11/1/2011)

  ---

  Have you tired the -v parameter in sqlcmd in combination with using another environment (like Powershell) to do the date calculation and set it to a variable to then pass into, and generate, your sqlcmd expression? Or have one job step use tsql to do your filename date string expression and output it to a file for sqlcmd to then read in from on a subsequent job step?

  Let me know if either approach sounds reasonable and I can offer some help with syntax if desired. Thanks,

  --tz

  I think the powershell option is better, I am not that good at powershell though, so any help will be appreciated.

• Roust_m

  SSCoach

  Points: 17410

  More actions

  November 2, 2011 at 7:22 pm

  #1402481

  Found the solution, if someone else would ever need it:

  $timestamp = (get-date).AddDays(-1);

  $timestamp = date $timestamp -format yyyy-MM-dd

  $DBScriptFile = "\\MyServer\Scripts\MyDb\08_SPs " + $timestamp + ".sql"

  Invoke-Sqlcmd -ServerInstance localhost -database sps -InputFile $DBScriptFile

• TZ.DBGeek

  SSC Enthusiast

  Points: 183

  More actions

  November 8, 2011 at 6:45 am

  #1404482

  Thanks for sharing Roust_m!! Glad it worked out for you