How to run a stored procedure every day in SQL Server Express Edition?

Question

How is it possible to run a stored procedure at a particular time every day in SQL Server Express Edition?

Notes:

• This is needed to truncate an audit table
• An alternative would be to modify the insert query but this is probably less efficient
• SQL Server Express Edition does not have the SQL Server Agent

Related Questions:

• How can I schedule a daily backup with SQl Server Express?
• Scheduled run of stored procedure on SQL Server

Answer 1

Since SQL Server express does not come with SQL Agent, you can use the Windows scheduler to run a SQLCMD with a stored proc or a SQL script.

http://msdn.microsoft.com/en-us/library/ms162773.aspx

Answer 2

I found the following mechanism worked for me.

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.