Schedule a Stored Procedure to run overnight

What I want to do is to create a Web Interface to allow a User to click a
link and execute a Stored Procedure, I think this would be straight forward.
What I also want to do is to allow the user to select a check box to have
that stored procedure run over night or even schedule the Stored procedures
to run at differnet times.
through E-SQL I would just set up an Agent to do the job, the User won't
have that privelage they will have to go through a web page.
I am not even sure what approach to take? Is there a way to setup an agent
to run via stored procedures so through my code I can just call a system
stored procedure that will create the agent and schedule the job?
Thanks
SteveCreate a queue Table to hold the requests by users for Jobs to run, and a SQ
L
Agent Job to run every tem minutes, or whatever, to read the records out of
that table, and execute them if the current system datetime has passed the
datetime as stored in the queue table.. Then delete the queue table record
(or, - better, flag it as completed, when the job has run successfully.
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||See sp_add_job, sp_add_jobstep, sp_add_jobschedule, etc. in BOL.
AMB
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||a Queue table is not a bad idea, but I would have to write the name of a
Stored Procedure in a field and then have the agent read that name and
execute the named procedure - I am not sure how to do that but I like the
thought I will look into it.
"CBretana" wrote:
> Create a queue Table to hold the requests by users for Jobs to run, and a
SQL
> Agent Job to run every tem minutes, or whatever, to read the records out o
f
> that table, and execute them if the current system datetime has passed the
> datetime as stored in the queue table.. Then delete the queue table record
> (or, - better, flag it as completed, when the job has run successfully.
> "Steve K" wrote:
>|||Thanks I will look up these sp's and see how to use them
"Alejandro Mesa" wrote:
> See sp_add_job, sp_add_jobstep, sp_add_jobschedule, etc. in BOL.
>
> AMB
> "Steve K" wrote:
>|||Steve,
After some research, Alej's idea is much simpler. (use sp_add_job)
I discovered you can even add the job with a flag set (@.delete_level =
3) that will cause the job to execute once and then delete iteself, which wa
s
the main reason I would have rolled-my-own system to do what you are trying
to do...
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||I think there is a disconnect here. The solution that Charly is recommending
involves writing a record into a queue table and then having a scheduled job
execute the stored proc using the queue value to determine the data on which
to
act. The job would have the stored proc that should be executed hard coded i
nto
its list of steps.
You aren't asking to execute any stored proc in the system like right?
Thomas
"Steve K" <SteveK@.discussions.microsoft.com> wrote in message
news:0C18A082-88DF-4924-B65B-B4AB13EAEF55@.microsoft.com...
>a Queue table is not a bad idea, but I would have to write the name of a
> Stored Procedure in a field and then have the agent read that name and
> execute the named procedure - I am not sure how to do that but I like the
> thought I will look into it.
> "CBretana" wrote:
>|||sounds good that's the direction I am going to run.
Thanks
Steve
"CBretana" wrote:
> Steve,
> After some research, Alej's idea is much simpler. (use sp_add_job)
> I discovered you can even add the job with a flag set (@.delete_level =
> 3) that will cause the job to execute once and then delete iteself, which
was
> the main reason I would have rolled-my-own system to do what you are tryin
g
> to do...
> "Steve K" wrote:
>|||No, I would have a seperate table that contains a list of avalable stored
procedured users can run on demand from this table I would probably just
throw a grid on the screen with an execute link and allow the user to select
execute, the difficult part here is if the user wants to schedule that job t
o
run overnight then I would need some way to know which Sp they selected and
what time to run it.
does that make sense?
"Thomas Coleman" wrote:

> I think there is a disconnect here. The solution that Charly is recommendi
ng
> involves writing a record into a queue table and then having a scheduled j
ob
> execute the stored proc using the queue value to determine the data on whi
ch to
> act. The job would have the stored proc that should be executed hard coded
into
> its list of steps.
> You aren't asking to execute any stored proc in the system like right?
>
> Thomas
>
> "Steve K" <SteveK@.discussions.microsoft.com> wrote in message
> news:0C18A082-88DF-4924-B65B-B4AB13EAEF55@.microsoft.com...
>
>|||Do I understand what you are planning on doing? Yes, I think so. Do I unders
tand
why you are doing it? No, but that may not be important ;->
I'm generally inclined to abstract that functionality provided to users away
from the means by which it is implemented. Thus, I wouldn't have users selec
ting
stored procedures per se and it may be the case that you are not either. Sin
ce
I'm presuming that the set of functionality provided to the user is finite,
I'd
be inclined to either make multiple queues and multiple jobs for each stored
proc or perhaps a single queue and multiple jobs for each stored proc. Users
would be presented with a list of actions to perform such that behind the sc
enes
would be a mapping of actions to stored procedures. If the user asked for a
scheduled action, the system would map that request to a post into the
appropriate queue table. That queue table would store the time and day that
the
stored procedure should be executed and the scheduled job would be set to po
ll
continuously (say every minute or every five minutes) to determine if there
is
work to do.
However, since I don't know the specifics of the problem you are attempting
to
solve, I may be completely off the mark.
Thomas
"Steve K" <SteveK@.discussions.microsoft.com> wrote in message
news:B4674B29-52CA-4320-9408-6C47600FEBB7@.microsoft.com...
> No, I would have a seperate table that contains a list of avalable stored
> procedured users can run on demand from this table I would probably just
> throw a grid on the screen with an execute link and allow the user to sele
ct
> execute, the difficult part here is if the user wants to schedule that job
to
> run overnight then I would need some way to know which Sp they selected an
d
> what time to run it.
> does that make sense?
> "Thomas Coleman" wrote:
>