19

I have an ASP.NET MVC app which works with database under SQL Server 2008 R2 Express edition. There is a need to perform a regular task on updating some records in the database.

Unfortunately the Express Edition lacks SQL Agent.
What approach would you recommend?

Sandeep Kumar M
  • 4,682
  • 3
  • 33
  • 35
rem
  • 1,555
  • 3
  • 20
  • 22

4 Answers4

15

You can check out the Standalone SQL Agent open source project. It uses the native SQL Agent tables and procedures (which still exist in SQL Express) to store the jobs replacing the SQL Agent itself. The engine is pretty well done for T/SQL and Dos commands. Things like SSIS, etc aren't done yet. Also the editor is only about 1/2 done, but the engine to run the jobs is the important part.

mrdenny
  • 27,106
  • 2
  • 44
  • 81
9

Use ExpressMain, its a very handy solution for SQL Server Express users.

ExpressMaint

Project Description

This project maintains two utilities for automating the backup and maintenance of databases for SQL Server 2005/2008/2008R2 Express Edition. They are based on the sqlmaint application that came with SQL Server 2000 but updated for SQL Server 2005/2008/2008R2. There is a standalone VB.NET console application and a SQL stored procedure, that demonstrate how to automate database backup and maintenance tasks via SMO (SQL Management Objects) or TSQL.

These two articles will help you to use it
http://www.sqldbatips.com/showarticle.asp?ID=27
http://www.sqldbatips.com/showarticle.asp?ID=29

Community
  • 1
Sandeep Kumar M
  • 4,682
  • 3
  • 33
  • 35
8

You could use the Windows built-in task scheduler to run a SQL script using the command line tools that are provided with SQL Express - I believe that http://msdn.microsoft.com/en-us/library/ms162773.aspx is included with all SQL 2005 and 2008 editions including Express.

I would recommend setting up a user specifically for this and running the script logged via Windows Integrated Authentication. If you do use user/pass based authentication make sure you properly secure the file containing the password, particularly if the machine is a shared one. As well as being more fine grained about what users can do what on your database the other advantage of running the task as a different Windows user is that you don't need to mess around ensuring no console windows open to get in your way if it runs while you are logged in (if you run it as yourself, you might find a console window pops up and claims focus when the task runs).

David Spillett
  • 32,593
  • 3
  • 50
  • 92
6

I've had success in the past with the Teratrax Job Scheduler: http://www.teratrax.com/tjs/

You can basically just set up SQL scripts to run on a schedule. Very easy to use and has a handy little GUI for managing your schedules. It also has a built in email notification system that you can use for notifications on success/failure etc.

The downside over the previously mentioned solutions is that it comes with a $99 price tag, although there is a free trial should you wish to take a look.

GShenanigan
  • 255
  • 2
  • 8