August 22, 2008 at 3:03 pm
Hi,
I am looking to create a new SQL Server Job - and I'd like to be able to pass a Date value to this new Job, which will pass the Date value to Stored Procedures / Batch files.
I've been looking through Google...haven't found anything.
Is this possible?
Thank you for helping,
Jason
August 22, 2008 at 6:53 pm
How do you want to pass the date value? Do you want to get the current date? Is it a value stored in a database somewhere?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 8:51 am
Jack Corbett (8/22/2008)
How do you want to pass the date value? Do you want to get the current date? Is it a value stored in a database somewhere?
The date will be completely dynamic.
My plan is to have one job, that calls 4 DOS .bat files.
I want to pass a date to each of these 4 DOS .bat files.
August 25, 2008 at 9:10 am
What do you mean by completely dynamic? Do you mean you want to be able specify the value each time? For example I may run the job today with '8/1/08' and later run it for '8/10/08'.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 9:30 am
You can use a table and insert the date into the table. The job can query your table in order to get the correct date. I realize that this might not be the solution that you are looking for, but if the date should be dynamic and can not be computed each time, then I think that this is your only option
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 25, 2008 at 9:35 am
Jack Corbett (8/25/2008)
What do you mean by completely dynamic? Do you mean you want to be able specify the value each time? For example I may run the job today with '8/1/08' and later run it for '8/10/08'.
Exactly.
August 25, 2008 at 9:36 am
What I want is a Job, with 4 steps...each step calls a different DOS batch file, and I want to pass the SAME date to each batch file as a parameter.
So...today, I might pass "2008-08-01".
Tomorrow, I might pass "2008-08-10".
The next day I might pass "2007-07-14".
But I want to pass the SAME date to all 4 batch files.
How do I pass a dynamic value, to a batch file as a parameter?
Make sense?
August 25, 2008 at 9:42 am
Adi has provided the only solution I am aware of.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 9:51 am
My main question isn't how to store a date.
My question is, how do I pass a parameter to a batch file in a Job step?
August 25, 2008 at 9:56 am
In other words, my Job step is a CmdExec routine, calling this line:
"D:\scripts\step1.bat"
What I'm looking to do is instead, pass something like this:
"D:\scripts\step1.bat %1"
Where %1 is a stored value within the job.
Is this possible?
August 25, 2008 at 10:04 am
- I'd just like to note that I don't promote the use xp_cmdhsell ... use with caution.
Yes.... I never use xp_cmdshell anymore.
Is there ANY other way to pass a dynamic value to a CmdExec call?
August 25, 2008 at 10:12 am
Jason Wisdom (8/25/2008)
- I'd just like to note that I don't promote the use xp_cmdhsell ... use with caution.
Yes.... I never use xp_cmdshell anymore.
Is there ANY other way to pass a dynamic value to a CmdExec call?
I removed my post, had some errors in it, but good ... don't use it 🙂
You can use an Active X step and write vbscript code. You can create a date in vbscript, or make a call back to SQL to pull it from a control table. Never used CmdExec command. Is it nothing more than a command line?
August 25, 2008 at 10:12 am
I don't think you can do this dynamically directly from a job. You can call a stored proc in the job, build the parameter in the proc and call xp_cmdshell, or possibly another non-scheduled job and alter the job step in real time.
Maybe use sp_update_jobstep to do this.
August 25, 2008 at 10:46 am
Steve Jones - Editor (8/25/2008)
I don't think you can do this dynamically directly from a job. You can call a stored proc in the job, build the parameter in the proc and call xp_cmdshell, or possibly another non-scheduled job and alter the job step in real time.Maybe use sp_update_jobstep to do this.
Okay, so can a stored procedure run a CmdExec batch file, passing a parameter to that batch file?
Not using xp_cmdshell.
August 25, 2008 at 10:52 am
I don't believe so. Again, why not use an ActiveX job step and use VBScript if you really want to handle this in SQL?
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply