Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
similar question asked in SO –  Coder Hawk Jan 5 '11 at 12:29
add comment

4 Answers 4

up vote 7 down vote accepted

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

share|improve this answer
add comment

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

share|improve this answer
    
I once came across a site running SQL 6.5 on NT4 where they would use the built-in Windows AT command to launch Internet Explorer, which would load a Cold Fusion page which contained the SQL of their batch job... The funny thing was they did have SQL Executive (as ISTR it was called then), they just didn't know how to use it. –  Gaius Jan 21 '11 at 9:50
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.