| Author |
Topic |
|
cblash
Starting Member
7 Posts |
 Posted - 2009-11-17 : 15:07:55
|
| Is there a way to run a job from a stored procedure? I am programming something in .NET and need to be able to automate scheduling a task and pass in the name of the task, the start and end times, and the frequency of the task as parameters. It seems that the best option would be to run a job from a stored procedure if it is possible. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-17 : 15:26:35
|
| should know that even if the job fails the sp will succeed though. |
 |
|
|
cblash
Starting Member
7 Posts |
Posted - 2009-11-17 : 15:38:27
|
| Can you give me an example or give me a link I can refer to? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-17 : 15:44:36
|
| [code]Create Proc startAJobASExec sp_start_job 'my sql agent job';GO[/code]http://msdn.microsoft.com/en-us/library/ms186757(SQL.90).aspx |
|
|
|
cblash
Starting Member
7 Posts |
Posted - 2009-11-17 : 15:51:13
|
quote:
Originally posted by russell
Create Proc startAJobASExec sp_start_job 'my sql agent job';GO http://msdn.microsoft.com/en-us/library/ms186757(SQL.90).aspx
Thank you |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-17 : 15:54:11
|
You're welcome. Happy to help  |
|
|
|
cblash
Starting Member
7 Posts |
Posted - 2009-11-17 : 16:36:00
|
quote:
Originally posted by russell
You're welcome. Happy to help
Would I be able to pass in values from the stored procedure to the job? Sorry to ask so many questions but I have never created job before. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-17 : 16:56:30
|
| what is this job going to do? may be better to have the SP do the whole thing...?this might not be helpful but have a look here: http://msdn.microsoft.com/en-us/library/ms175575.aspx |
|
|
|
cblash
Starting Member
7 Posts |
Posted - 2009-11-17 : 17:44:44
|
quote:
Originally posted by russell
what is this job going to do? may be better to have the SP do the whole thing...?this might not be helpful but have a look here: http://msdn.microsoft.com/en-us/library/ms175575.aspx
Its a vb.net app that allows for scheduling an event to be run daily, weekly, monthly, or yearly with start date, end date, and how often to be scheduled passed in. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-17 : 19:46:16
|
| me too. seems op wants to create one |
|
|
|
cblash
Starting Member
7 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-18 : 10:31:12
|
| have a look at sp_add_jobsomehow though, i suspect you're taking the wrong approach. if you want the front end to schedule jobs, you can have it store the parameters in a table, and have a sql agent job look at this table at some set interval |
|
|
|
cblash
Starting Member
7 Posts |
Posted - 2009-11-18 : 10:45:19
|
quote:
Originally posted by russell
have a look at sp_add_jobsomehow though, i suspect you're taking the wrong approach. if you want the front end to schedule jobs, you can have it store the parameters in a table, and have a sql agent job look at this table at some set interval
That sounds more like what I am trying to do. There is a part of my vb.net app that allows the user to input how often they want an event to be scheduled. The event will only show up on or after the day it has been scheduled for. |
|
|
|
|
Run SQL Job from Stored Procedure