Need Dirxn --> How to schedule recurring stored procedure executio

Hi,
I need to schedule a stored procedure to run on a recurring basis. It would
be acceptable if the procedure ran 1x per minute though it would be better if
I could schedule & re-schedule procedure start time from within a trigger or
other stored procedure. The recurring stored procedure is NOT an
administrative maintenance task. The recurring procedure calls other stored
procedures across servers to gather data for the application.
Question:
1) What's the preferred way to schedule & reschedule recurring execution of
an application's stored procedures? From reading Books Online I gather that
SQL Server Agent and DTS can both schedule tasks? Is one of those two
approaches appropriate for my needs?
--RichardRichard,
You can place your stored proc invocation into a SQLAgent T-SQL job step.
You can invoke a SQLAgent job using sp_start_job. You can schedule this
job by creating a SQLAgent Job Schedule. You can change the Job Schedule
using sp_update_jobschedule.
So, your "other" stored proc can invoke sp_update_jobschedule to change
the frequency that the job executes.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Richard wrote:
> Hi,
> I need to schedule a stored procedure to run on a recurring basis. It would
> be acceptable if the procedure ran 1x per minute though it would be better if
> I could schedule & re-schedule procedure start time from within a trigger or
> other stored procedure. The recurring stored procedure is NOT an
> administrative maintenance task. The recurring procedure calls other stored
> procedures across servers to gather data for the application.
> Question:
> 1) What's the preferred way to schedule & reschedule recurring execution of
> an application's stored procedures? From reading Books Online I gather that
> SQL Server Agent and DTS can both schedule tasks? Is one of those two
> approaches appropriate for my needs?
> --Richard
>|||I agree with Mark, use SQL Agent for this.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:u7AHIUE3EHA.2312@.TK2MSFTNGP15.phx.gbl...
> Richard,
> You can place your stored proc invocation into a SQLAgent T-SQL job step.
> You can invoke a SQLAgent job using sp_start_job. You can schedule this
> job by creating a SQLAgent Job Schedule. You can change the Job Schedule
> using sp_update_jobschedule.
> So, your "other" stored proc can invoke sp_update_jobschedule to change
> the frequency that the job executes.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Richard wrote:
> > Hi,
> >
> > I need to schedule a stored procedure to run on a recurring basis. It
would
> > be acceptable if the procedure ran 1x per minute though it would be
better if
> > I could schedule & re-schedule procedure start time from within a
trigger or
> > other stored procedure. The recurring stored procedure is NOT an
> > administrative maintenance task. The recurring procedure calls other
stored
> > procedures across servers to gather data for the application.
> >
> > Question:
> >
> > 1) What's the preferred way to schedule & reschedule recurring execution
of
> > an application's stored procedures? From reading Books Online I gather
that
> > SQL Server Agent and DTS can both schedule tasks? Is one of those two
> > approaches appropriate for my needs?
> >
> > --Richard
> >

Need Dirxn --> How to schedule recurring stored procedure executio

Hi,
I need to schedule a stored procedure to run on a recurring basis. It would
be acceptable if the procedure ran 1x per minute though it would be better if
I could schedule & re-schedule procedure start time from within a trigger or
other stored procedure. The recurring stored procedure is NOT an
administrative maintenance task. The recurring procedure calls other stored
procedures across servers to gather data for the application.
Question:
1) What's the preferred way to schedule & reschedule recurring execution of
an application's stored procedures? From reading Books Online I gather that
SQL Server Agent and DTS can both schedule tasks? Is one of those two
approaches appropriate for my needs?
--Richard
Richard,
You can place your stored proc invocation into a SQLAgent T-SQL job step.
You can invoke a SQLAgent job using sp_start_job. You can schedule this
job by creating a SQLAgent Job Schedule. You can change the Job Schedule
using sp_update_jobschedule.
So, your "other" stored proc can invoke sp_update_jobschedule to change
the frequency that the job executes.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Richard wrote:
> Hi,
> I need to schedule a stored procedure to run on a recurring basis. It would
> be acceptable if the procedure ran 1x per minute though it would be better if
> I could schedule & re-schedule procedure start time from within a trigger or
> other stored procedure. The recurring stored procedure is NOT an
> administrative maintenance task. The recurring procedure calls other stored
> procedures across servers to gather data for the application.
> Question:
> 1) What's the preferred way to schedule & reschedule recurring execution of
> an application's stored procedures? From reading Books Online I gather that
> SQL Server Agent and DTS can both schedule tasks? Is one of those two
> approaches appropriate for my needs?
> --Richard
>
|||I agree with Mark, use SQL Agent for this.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:u7AHIUE3EHA.2312@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Richard,
> You can place your stored proc invocation into a SQLAgent T-SQL job step.
> You can invoke a SQLAgent job using sp_start_job. You can schedule this
> job by creating a SQLAgent Job Schedule. You can change the Job Schedule
> using sp_update_jobschedule.
> So, your "other" stored proc can invoke sp_update_jobschedule to change
> the frequency that the job executes.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Richard wrote:
would[vbcol=seagreen]
better if[vbcol=seagreen]
trigger or[vbcol=seagreen]
stored[vbcol=seagreen]
of[vbcol=seagreen]
that[vbcol=seagreen]