Scheduled run of stored procedure on SQL server

Question

Is it possible to set up somehow Microsoft SQL Server to run 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

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?