How to run a stored procedure every day in SQL Server Express Edition?
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
Related videos on Youtube
Solaiman Mansyur
Working on configuration, control and reporting backend for a fintech / machine learning startup based in Cambridge. Using Java 8, Apache Kafka, Apache Zookeeper, Elasticsearch, MongoDB, Oracle 12c. Previously: using C#, NServiceBus, ASP.NET MVC, MS SQL Server and Oracle 11g databases developing management and control software in C#, C++, C for high bandwidth video over ATM and IP networks embedded work with C / Motorola 68000
Updated on July 09, 2022Comments
-
Solaiman Mansyur almost 2 years
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:
-
Solaiman Mansyur over 14 yearsThis post looks interesting (referenced in an answer to one of the related questions): sqlteam.com/article/scheduling-jobs-in-sql-server-express
-
Solaiman Mansyur over 14 yearssp_procoption will allow a stored procedure to run automatically.
-
Cade Roux over 14 yearsIn conjunction with your audit trail, I would add to that stored proc a check of the process history, so that if it has not been run in 24 hours or whatever - to go ahead and run the process at startup. This would handle cases when the machine gets shut down over night or occasional nights or similar (a VM which is spun up on demand).
-
Solaiman Mansyur over 14 yearsGood point! For our case, the database runs continually as it is a customer system that is run 24x7. I wonder if the simplest thing to do is to run the 'MyDatabaseStoredProcedure' script before the wait? In our case this would work fine but it might not for a different application requirement.
-
VoteCoffee about 10 yearsI need to fix the task schedule command (via command line). It turns out it does not auto-restart on the next boot and I had to manually correct it. I will edit and fix the code later, just wanted people to be aware. The execution code works great though.
-
tufelkinder almost 10 yearsAre there any downsides to having a procedure running constantly in the background?
-
Raj More over 9 yearsCons: 1. Have to create a new Stored Proc every time you need to schedule a job 2. Reboot on every schedule change
-
Hugo Delsing about 9 yearsStill busy? Or do you have time to fix the command to make this answer complete?
-
VoteCoffee about 9 yearsI figured out the setting I needed was advanced and not supported by schtask.exe. I instead opted to have the vbs perform the repeat functionality and set it to run at startup. It's good now.
-
VoteCoffee about 9 yearsYou could get around having to reboot by creating a table with a running flag for each scheduled task. Set the flag at the top of the task's stored proc and use it in the while condition. Clearing the flag would cause it to stop after the current iteration completes. I'd also recommend having a table so you can store the last start time and duration for diagnostic purposes.
-
done_merson over 3 yearsThis link is dead.
-
done_merson over 3 yearsSQLScheduleer link is dead.
-
Eng Soon Cheah over 3 yearsProcedure sp_procoption, Statement 'CONFIG' is not supported in this version of SQL Server.