3

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 ??

Community
  • 1
  • 1
user1740381
  • 2,121
  • 8
  • 37
  • 61
  • 1
    I'm not exactly sure what to make of the `WAITFOR` method. It's plausible but there are other things that don't really pass the "code smell" test. I'd much rather see a Windows Scheduler job (which I've done in the past). – swasheck Feb 28 '14 at 18:59
  • 2
    You win the lottery and leave your current job. What is going to make more sense for the folks that support your position: using a dedicated scheduling tool versus some homegrown, janky script. And if you still think your infinite loop is a good idea, it's not. – billinkc Feb 28 '14 at 19:06
  • @swasheck thanks for your suggestion, can you please show me some example code of scheduling job using windows scheduler, i am not good in database. I am using EntityFramework with c# in my project – user1740381 Feb 28 '14 at 19:18

2 Answers2

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.

Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
1

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.

Anup Shah
  • 1,256
  • 10
  • 15