Passing parameters to sqlcmd

Question

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TZ.DBGeek SSC Enthusiast Points: 163 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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: 163 More actions · Answer 4

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