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

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 12/07/04 

Date: Tue, 7 Dec 2004 08:06:30 -0500

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
> >

Relevant Pages

  • Re: scheduling a t-sql stored procedures name in table column without creating a job for each on
    ... You want to schedule or simply run/put them on a Q and they run? ... Again I would still connect to SQL Server however it is that you want to do it, get all the possible procs from your Control table. ... I want to write a stored procedure that will ... You also do not need to use a Script task to do ...
    (microsoft.public.sqlserver.dts)
  • Re: Limitations of Scheduled jobs
    ... > Actually we can schedule the jobs with accuracy less than ... where executing the stored procedure ... >>on what the stored procedure does exactly. ... >>SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)
  • Re: Accessing FoxPro Free Table
    ... which the SQL Server service is running. ... account, ... > If you are creating a stored procedure and you want> to make sure that the procedure definition cannot be> viewed by other users, you can use the WITH ENCRYPTION> clause. ... The procedure definition is then stored in an> unreadable form. ...
    (microsoft.public.data.odbc)
  • Re: Adding date to parameter
    ... SqlClient provider which is specifically written for SQL Server. ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... I made the assumption that the stored procedure went by the ...
    (microsoft.public.dotnet.framework.adonet)
Loading