Create jobs in SQL Server Express edition? [duplic

Question

This question already has an answer here:

• SQL Server Agent Job Set Up 2 answers

I want to schedule some jobs in sql server express edition. After digging a bit i got few post like:

How to run a stored procedure every day in SQL Server Express Edition?

How to create jobs in SQL Server Express edition

How to run a stored procedure every day in SQL Server Express Edition?

and i understand that Sql Agent is require to schedule a job in sql server but since SQL Server express does not come with SQL Agent so i have to go with some other alternative.

And i got this, which suggest good and easy alternative. Here in @Thomas Bratt's answer he used infinite loop and waitfor (Transact-SQL)

Code from @Thomas Bratt's answer

I want to know the performance impact (if any) of this code.

...
while 1 = 1
begin
    waitfor time @timeToRun
    begin
        execute [MyDatabaseName].[dbo].[MyDatabaseStoredProcedure];
    end
end 
...

and in the same post @Raj More suggest another alternative i.e Windows Scheduler, so which one is better to use Windows Scheduler or waitfor (Transact-SQL) with infinite loop ??

Answer 1

I suggest creating a Windows Scheduled Task instead and use it in conjunction with SqlCmd utility to run tasks on schedule.

If you need to create a Windows Task programmaticaly, there're numerous .NET wrappers (e.g. http://taskscheduler.codeplex.com/)

As an alternative, you can use QUARTZ.NET scheduler in your own code. Using it you can schedule any any .NET class execution.

Answer 2

yes, "sp_procoption" is meant for the the automatic execution of procedure. but in my opinion those procedures would have some logic to perform one time settings updates and/or configuration changes on all databases. if your SQL jobs are also meant for similar taks then you should consider this option with limitation in mind that these procs can not have any parameters. Also from the performance impact of the while loop, you will always see this as a background running job for your SQL server but most of the time sleeping/waiting to time occur. actual performance hit is depends upon what tasks it is doing when it runs and not when it is waiting.

bottom line: if I am planing to or expecting more enhancements in this SQ jobs in near future I wouldn't overload the "sp_procoption" feature by using it as a job scheduler. instead i would consider more flexible options as other users have suggested.