Sheduled run of stored procedure on MS SQL server

Question

Is it possible to set up somehow Microsoft SQL Server to run automatically a stored procedure on regular basis?

Answer 1

Yes, in MS SQL Server, you can create scheduled jobs. In SQL Management Studio, navigate to the server, then expand the SQL Server Agent item, and finally the Jobs folder to view, edit, add scheduled jobs.

Answer 2

Using Management Studio - you may create a Job (unter SQL Server Agent) One Job may include several Steps from T-SQL scripts up to SSIS Packages

Jeb was faster ;)

Answer 3

You should look at a job scheduled using the SQL Server Agent.

Answer 4

Set up a scheduled job.

Answer 5

Yes, if you use the SQL Server Agent.

Open your Enterprise Manager, and go to the Management folder under the SQL Server instance you are interested in. There you will see the SQL Server Agent, and underneath that you will see a Jobs section.

Here you can create a new job and you will see a list of steps you will need to create. When you create a new step, you can specify the step to actually run a stored procedure (type TSQL Script). Choose the database, and then for the command section put in something like:

exec MyStoredProcedure

That's the overview, post back here if you need any further advice.

[I actually thought I might get in first on this one, boy was I wrong :)]

Answer 6

I'll add one thing: where I'm at we used to have a bunch of batch jobs that ran every night. However, we're moving away from that to using a client application scheduled in windows scheduled tasks that kicks off each job. There are (at least) three reasons for this:

1. We have some console programs that need to run every night as well. This way all scheduled tasks can be in one place. Of course, this creates a single point of failure, but if the console jobs don't run we're gonna lose a day's work the next day anyway.
2. The program that kicks off the jobs captures print messages and errors from the server and writes them to a common application log for all our batch processes. It makes logging from withing the sql jobs much simpler.
3. If we ever need to upgrade the server (and we are hoping to do this soon) we don't need to worry about moving the jobs over. Just re-point the application once.

It's a real short VB.Net app: I can post code if any one is interested.

Answer 7

If MS SQL Server Express Edition is being used then SQL Server Agent is not available. I found the following worked for all editions:

USE Master
GO

IF  EXISTS( SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(N'[dbo].[MyBackgroundTask]')
            AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[MyBackgroundTask]
GO

CREATE PROCEDURE MyBackgroundTask
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- The interval between cleanup attempts
    declare @timeToRun nvarchar(50)
    set @timeToRun = '03:33:33'

    while 1 = 1
    begin
        waitfor time @timeToRun
        begin
            execute [MyDatabaseName].[dbo].[MyDatabaseStoredProcedure];
        end
    end
END
GO

-- Run the procedure when the master database starts.
sp_procoption    @ProcName = 'MyBackgroundTask',
                @OptionName = 'startup',
                @OptionValue = 'on'
GO

Some notes:

• It is worth writing an audit entry somewhere so that you can see that the query actually ran.
• The server needs rebooting once to ensure that the script runs the first time.
• A related question is: How to run a stored procedure every day in SQL Server Express Edition?

Answer 8

Hey Joel,

I'd be interested in seeing the VB.net program to run multiple stored proceedures.

Thanks!

Answer 9

Hi Joel, I am also interested to have a look at this VB.Net program.. 1. Will it be running all the time on the server.. 2. If Yes, then how much resource it will consume on a regular basis.. 3. Another concern is if we can use this on third party hosting servers..

The idea looks to be very interesting specially in terms of moving/shifting database servers.. Logging will be another added feature as described here..

Thanks..