How to Run a Stored Procedure Every Day in SQL Server Express Edition

Execute a procedure on a specific day in Express Edition

Since you are using SQL Express you can't use SQL Server Agent. However there are many alternatives, all of which you can schedule using schtasks or Windows Task Scheduler depending on your operating system:

• VBScript
• C# command line app
• batch file with SQLCMD
• PowerShell

All of these languages/tools (and many others) have the capacity to connect to SQL Server and execute a stored procedure. You can also try these Agent replacements:

• SQLScheduler
• Express Agent
• Standalone SQL Agent (beta)

Here is an example using VBScript:

1. Open Notepad. Type the following code, replacing $tokens$ with your real values:

   s = "$your server/instance name$"
   db = "$your database name$"
   u = "$your SQL auth user ID$"
   p = "$your SQL auth password$"
   constr = "provider=SQLOLEDB;Data Source=" & s & ";Initial Catalog=" & s & _
            ";User ID=" & u & ";Password=" & p & ";Network=DBMSSOCN;"
   Set conn = CreateObject("ADODB.Connection")
   conn.Open constr
   conn.Execute "EXEC dbo.$your procedure name$;"
   conn.close: set conn = nothing
   

2. Save this file as c:\somewhere\ScheduledProcedure.vbs

3. Open a command prompt and type the following (this will schedule this script to run on the 15th of every month at midnight), all on one line:

   schtasks /create /tn "some name" 
     /tr "wscript ""c:\somewhere\ScheduledProcedure.vbs""" 
     /sc monthly /d 15 /st 00:00
   

To see this task in your list of scheduled tasks (there are a lot, but it should be in the very first group):

schtasks /query

If you later want to delete it:

schtasks /delete /tn "some name" /F

How to run stored procedure every second automatically (sql server 2014 Express)?

This is a tough one. You're on the right track that you need an outside method to run the job, but I'd recommend writing one yourself to do it if you need the frequency every second (and also question if you really need it every second or not). A service or light application would be the best way to handle it.

I've written an article on setting up Task Scheduler for automated jobs on SQL Server Express, but that has a limit of once/minute. You would use the sqlcmd utility in this case.

You can look at third party utilities like JAMS Scheduler, but their community edition is only free for a year.

That being said, I don't know of any quick-and-easy methods that satisfy your frequency requirements as stated.

Scheduled run of stored procedure on SQL server

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.

How to run a stored procedure automatically every day

Set up a SQL job http://msdn.microsoft.com/en-us/library/ms135739.aspx

How to run a stored procedure in sql server every hour?

In SSMS navigate to SQL Server Agent-->Jobs
Right click on the Job Folder and select new job

on the dialog that pops up, give the job a name
click on steps, then on new, you will see a dialog like the following, pick correct DB and type your proc name

after that click on schedule, pick new and you will see something like the image below, fill all the stuff you need and click ok, click ok on the job and you should be set

How to schedule to run a query every day in SQL Server Express and save the result in CSV format?

PS script with task manager is the simplest way to go. however, you will need a service account to set up the task.

I assume you need the task to run whether a user is logged on or not. A service account in the simplest form is a domain account that is not tied to any users. That way, your task will not fail when your password changes or expires or ur account gets deleted (in the event that you leave the company). You can specify to use a domain account in the general tab after creating your task.

---

Related Topics