Scheduled tasks

Is it possible to schedule tasks in SQL Express, for example a database backup, or to periodically execute a stored procedure?

hi Allan,

yes, it is possible, but not using the SQL Server Agent as SQLExpress does not provide the relative bits.. you have so to rely on the native OS scheduler, third party (or self written alternative) scheduler, or rely on the Service Broker features..

personally I usually go for the native OS scheduler, where you can define a list of xx.sql scripts to be executed by a xx.cmd file.. then the xx.cmd file, connecting to SqlCMD.exe and executing each defined xx.sql script, has to be scheduled... quiete simple and not expensive... results are output to a text file similar to:

[cmdfile.cmd]

ECHO. >>Backing up databases

SqlCmd -E -S(Local)\InstanceName -i"c:\somefolder\ScheduledBackup.sql" >c:\SomeFolder\ScheduledBackupLog.txt

ECHO. >>Sending results via mail to aministrators

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Mailing log of - ' + CONVERT(varchar, GETDATE());" >E:\e-VbHot\Files\Log\MailingLogBCKschedulato.txt
SqlCmd -E -S(Local)\InstanceName -Q"SET NOCOUNT ON; DECLARE @.ret int;EXEC @.ret = [applicationDB].[dbo].[amSMTPmail] @.Server = N'smtp server name', @.Sender = N'scheduledbackup@.my_company.com', @.AddressesTO = N'admin_name@.my_company.com', @.AddressesCC = N'me@.me.com', @.AddressesCCN = NULL, @.AttachFiles = N'c:\SomeFolder\ScheduledBackupLog.txt', @.Subject = N'Backup performed', @.MessageBody = N'Backup performed', @.UserName = NULL, @.UserPassword = NULL; SELECT @.ret AS [Execution result];" >>c:\SomeFolder\MailingLog.txt

[/cmdfile.cmd]

as you can see, I do personally even send the c:\SomeFolder\ScheduledBackupLog.txt resulting file via e-mail to a "list" of addresses.. this is performed via a (free) CLR stored procedure of mine, amDBObj, where the .Net code implements a poor's man SMTP mail feature to "replace" (ok... it is not comparable with) Database Mail missing feature of SQLExpress..

regards

|||

Thanks for the info Andrea. I will look into it.

Thanks, too, for the offer of the CLR email proc - but I already did one myself :)

sql