November 1, 2011 at 7:22 pm
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
November 1, 2011 at 8:08 pm
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
November 1, 2011 at 8:12 pm
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.
November 2, 2011 at 7:22 pm
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
November 8, 2011 at 6:45 am
Thanks for sharing Roust_m!! Glad it worked out for you
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy