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

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

Solution 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

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

Solution 3:

Create a scheduled task that calls "C:\YourDirNameHere\TaskScript.vbs" on startup. VBScript should perform repeated task execution (in this example, it's a 15 minute loop)

Via command line (must run cmd.exe as administrator):

schtasks.exe /create /tn "TaskNameHere" /tr "\"C:\YourDirNameHere\TaskScript.vbs\" " /sc ONSTARTUP

Example TaskScript.vbs: This executes your custom SQL script silently using RunSQLScript.bat

Do While 1
    WScript.Sleep(60000*15)
    Set WshShell = CreateObject("WScript.Shell")
    WshShell.RUN "cmd /c C:\YourDirNameHere\RunSQLScript.bat C:\YourDirNameHere\Some_TSQL_Script.sql", 0
Loop

RunSQLScript.bat: This uses sqlcmd to call the database instance and execute the SQL script

@echo off
sqlcmd -S .\SQLEXPRESS -i %1

Solution 4:

If you are using Express Edition, you will need to use the Windows Scheduler or the application connecting to the server in some way.

You would use the scheduler to run sqlcmd. Here are some instructions for getting the sqlcmd working with express edition.

Solution 5:

SQL Scheduler from http://www.lazycoding.com/products.aspx

• Free and simple
• Supports all versions of SQL Server 2000, 2005, and 2008
• Supports unlimited SQL Server instances with an unlimited number of jobs.
• Allows to easily schedule SQL Server maintenance tasks: backups, index rebuilds, integrity checks, etc.
• Runs as Windows Service
• Email notifications on job success and failure