Need help pass in date parameter into bat file.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Need help pass in date parameter into bat file.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-02-13 : 12:04:17

Is it possible to schedule sql job to execute bat files and pass in date below.
I want to take a system date and pass into the bat file below. A Schedule Job will run on Monday at 9 P.M.
I want to automate this process instead manually enter into the bat file. Is that possible?
Any suggestions is appreciate. SQL 2012

Thank you so much in advance.

SQL Schedule job

DECLARE @sMonday VARCHAR(25) = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '')
SET @sMonday = @sMonday + '_qa48_Clonedb'
SELECT @sMonday

--021315_qa48_Clonedb

DECLARE @sPrevMonday VARCHAR(25) = CAST(REPLACE(CONVERT(CHAR(10), DATEADD(DAY, - (DATEPART(dw, GETDATE() ) - 1), GETDATE() ),1), '/', '') AS CHAR(6))
SET @sPrevMonday= @sPrevMonday + '_qa48_Clonedb'

SELECT @sPrevMonday

--020815_qa48_Clonedb
----------------------------------------------------------------------------------------------------

Result want:

-- Testbatfile.bat

REM 021615_qa48_Clonedb

SET sToday = @sMonday
SET sPreviousWeek = @sPrevMonday
SET sNumOfDay=07

DiskMirror -l snapsrvcfdba n4brec01a -i privkey.ppk "snap rename sql32oltp_repl_dataf sqlsnap__brpRMSDVS050a__recent %sToday% "
DiskMirror -l snapsrvcfdba n4brec01a -i privkey.ppk "snap rename sql32oltp_repl_logf sqlsnap__brpRMSDVS050a__recent %sToday% "

DiskMirror -l snapsrvcfdba l9ABC01d -i privkey.ppk "snap rename sql100oltp_repl_dataf %sPreviousWeek% sqlsnap__ABCPWSCVR050a__%sNumOfDay% "

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-13 : 13:30:41

You can schedule jobs using Sql Server Agent. to execute a batch job, use a job step of type Operating System (cmd.exe). you can use tokens to get things like current date. see:

https://msdn.microsoft.com/en-us/library/ms175575.aspx

Alternatively, if you have xp_cmdshell enabled, you can run it all from a SQL query or stored procedure that can be scheduled in Agent.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources