Article: Scheduling Jobs in SQL Server Express - Part 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Scheduling Jobs in SQL Server Express - Part 2

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2008-12-01 : 08:14:37

In my previous article Scheduling Jobs in SQL Server Express we saw how to
make simple job scheduling in SQL Server 2005 Express work. We limited the scheduling to one time or daily repeats. Sometimes this isn't enough.
In this article we'll take a look at how to make a scheduling solution based on Service Broker worthy of the SQL Server Agent itself.

Read Scheduling Jobs in SQL Server Express - Part 2

manishkaushik
Starting Member

14 Posts

Posted - 2009-01-07 : 07:06:28

Thank you so much for the very informative article.
I successfully scheduled the "run once" job and it works like cream.
but when I scheduled the "daily" job, its not working properly, neither it gives me any kind of error and also none is being updated on SchedulingErrors table.

I don't know whats the wrong I did, If any one will face like me, lets share.

Thanks
Manish

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-07 : 07:09:19

what does your Schedule look like?
try running just dbo.GetNextRunTime to see what the next run time should be.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

manishkaushik
Starting Member

14 Posts

Posted - 2009-01-07 : 07:33:18

Thanks for the quick response, I tried by the following way....

----------------------------------------------------------------------------------------------------------------------------------------
DECLARE @JobScheduleId INT,
@ScheduledJobId INT,
@validFrom DATETIME,
@ScheduledJobStepId INT,
@secondsOffset INT,
@NextRunOn DATETIME

SELECT @validFrom = GETUTCDATE(),@secondsOffset = 28800,@NextRunOn = DATEADD(n, 1, @validFrom)
EXEC usp_AddJobSchedule @JobScheduleId OUT,
@RunAtInSecondsFromMidnight = @secondsOffset,
@FrequencyType = 1,
@Frequency = 1 -- run every day
EXEC usp_AddScheduledJob @ScheduledJobId OUT, @JobScheduleId, 'test job daily', @validFrom
DECLARE @backupSQL NVARCHAR(MAX)
SELECT @backupSQL = N'DECLARE @backupTime DATETIME, @backupFile NVARCHAR(512);
SELECT @backupTime = GETDATE(),
@backupFile = ''C:\Temp\MYDB_'' +
replace(replace(CONVERT(NVARCHAR(25), @backupTime, 120), '' '', ''_''), '':'', ''_'') +
N''.bak'';
BACKUP DATABASE MYDB TO DISK = @backupFile;'

EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, @backupSQL, 'step 1'

EXEC usp_StartScheduledJob @ScheduledJobId
----------------------------------------------------------------------------------------------------------------------------------------

and by executing dbo.GetNextRunTime it returns me the accurate next run time.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-07 : 09:26:38

well as i can see your next run time is one minute in the future. however a backup might run longer and thus be blocked by another.

also check the sys.transmission_queue for any errors and also see if your queue is enabled.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

manishkaushik
Starting Member

14 Posts

Posted - 2009-01-07 : 10:11:26

No, I configured the @secondsOffset = 28800, hence the next run time will be the 8 in the morning of the selected day.
and I will check the sys.transmission_queue.

Thanks
Manish

Thanks
Manish

manishkaushik
Starting Member

14 Posts

Posted - 2009-01-07 : 23:08:56

Yeah I checked the sys.transmission_queue , but there is not any entries for any errors.
and i seen that sys.service_queues , there i found the "ScheduledJobQueue" and is_enqueue_enabled = 1

Thanks
Manish

manishkaushik
Starting Member

14 Posts

Posted - 2009-01-09 : 07:48:29

Hi Spirit1,

After doing some workaround and I started with the fresh creation of the tables, sp's, & functions,
now i am able to execute the daily scheduled jobs properly.
Thanks for your precious time and wonderful article.

Thanks
Manish

vermeer_paul
Starting Member

2 Posts

Posted - 2009-01-13 : 22:03:08

Hi,

I'm facing the same problem. My queue does work properly. The function "dbo.GetNextRunTime" returns a correct datetime value.

BUT.... the dialog_timer within sys.conversation_endpoints shows '1900-01-01 00:00:00.000' as a value after the first execution of the job.

Some hints or ideas?

Cheers.

manishkaushik
Starting Member

14 Posts

Posted - 2009-01-13 : 23:34:14

That is fine, I am also getting the same.

Before execution of the job, the "dialog_timer" column of "sys.conversation_endpoints" table should show the time as it is in the "NextRunOn" column of the "ScheduledJobs" table and after execution of the job "dialog_timer" column of "sys.conversation_endpoints" would have the "1900-01-01 00:00:00.000".

What is wrong in your case. as you said your queue works properly, means the job is done.

Thanks
Manish

vermeer_paul
Starting Member

2 Posts

Posted - 2009-01-15 : 15:05:47

Hi Manish, others,

the REscheduling of a task does not work properly due to several reasons.

a REschedule is blocked by the following statements in usp_startscheduledJob: (IsEnabled is 1 for rescheduling)

DECLARE @TimeoutInSeconds INT, @NextRunOn DATETIME, @JobScheduleId INT

SELECT @ValidFrom = ValidFrom, @NextRunOn = NextRunOn, @JobScheduleId = JobScheduleId
FROM ScheduledJobs
WHERE ID = @ScheduledJobId AND IsEnabled = 0

IF @@ROWCOUNT = 0
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK;
RETURN;
END

The fault is comming from usp_runScheduledJobs. The exec call :

EXEC usp_StartScheduledJob @ScheduledJobId, @ConversationHandle

It does not have a an @validFrom parameter and therefore triggers the usp_start code in an incorect way. Daily schedules don't get rescheduled due to this.

IN ADDITION:
FOLLOWING CODE ENABLES YOU TO CLEAN ANY TESTS YOU'VE DONE (QUICK AND DIRTY)

use [test]

declare @i int
set @i = 1
while @i <> 1000
begin
declare @conversation_handle uniqueidentifier
declare cur CURSOR for
SELECT TOP (1000) conversation_handle
FROM sys.conversation_endpoints
open cur
fetch next from cur into @conversation_handle
while @@fetch_status = 0
begin
end conversation @conversation_handle with cleanup
fetch next from cur into @conversation_handle
end
close cur
deallocate cur set @i = @i + 1
end

DELETE FROM dbo.SchedulingErrors
DELETE FROM dbo.ScheduledJobSteps
DELETE FROM dbo.ScheduledJobs
DELETE FROM dbo.JobSchedules

GO

manishkaushik
Starting Member

14 Posts

Posted - 2009-01-16 : 10:31:15

Hi Vermeer,

Yeah, even I am having few issues with rescheduling of tasks.
I will try again, See what I was doing and expecting.

Lets say, The job is scheduled every day at 10 :30 Pm, and it executed successfully today (16th Jan) at 10:30 pm , then I changed the system date to (17th Jan) and adjusted the clock to 10:27 pm, and was expecting the job to execute at 10:30 pm. But this does not happened. I do not know where I am missing any thing.

Thanks
Manish

dracdliw
Starting Member

1 Post

Posted - 2009-01-21 : 14:48:15

The way I calculate it a day contains 86400 seconds, but your check constraint on the RunAtInSecondsFromMidnight column in the JobSchedules table has a check constraint for values between 0 and 84599 (84600 seconds). You are losing 30 minutes.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-21 : 15:10:14

hey dracdliw, thanx for noticing this. it's a perfect typo

.
however this is a problem for only last 30 minutes of the day. so you can't set schedules for the last half hour of the day.
for other times this should work ok.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

PatrickW
Starting Member

2 Posts

Posted - 2009-03-14 : 01:18:29

I was trying the example code and got the below error in the SQL Server Studio Express output. I ran the first 2 tests 'run once' and 'SIMPLE DAILY SCHEDULING EXAMPLE' without changing anything.
Any ideas? Thnx.

------------output------------------

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 208, Level 16, State 1, Procedure usp_AddScheduledJob, Line 17
Invalid object name 'JobSchedules'.
Msg 50000, Level 16, State 1, Procedure usp_AddScheduledJob, Line 24
@NextRunOn parameter has to be in the future in the UTC date format.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure usp_StartScheduledJob, Line 83
NextRunOn date for scheduled job ID 6 is les than current UTC date.

Tacola
Starting Member

2 Posts

Posted - 2009-03-16 : 01:29:58

I have having a simlar problem to one mentioned eariler.

"dialog_timer" column of "sys.conversation_endpoints" would have the "1900-01-01 00:00:00.000".

I installed the scheduler on site, Sql Server 2005. It works fine. When the time hit, the SP fired and even the endpoints record was deleted. That was a Go-Daddy site.
I backed up the DB and restored it to another site. Now when the time hits dialog_timer is set to 1900 but the SP doesn't fire. Any ideas, may be a rights issue or settings?
Thanks in advance for any response. I do have it running quite nicely on one box. With 5 queues feeding into the scheduleJobs table. thanks for the code =). Any help would be appreciated.

akan
Starting Member

1 Post

Posted - 2009-04-03 : 11:22:10

why go to all this trouble when you can reuse existing robust functionality inside windows called ......... scheduled Tasks! (available via control panel).

Use the scheduler to specify the required run schedule and then use :

osql.exe -S servername -d database -U username -P password -Q "EXEC myScheduledProc"

as the command to run. This will call and run your procedure for you per schedule without any need to re-invent the wheel.

mvandoni
Starting Member

12 Posts

Posted - 2009-04-07 : 11:15:18

I found a small bug in the schedule process.
I'm trying it on my personal pc and what I see is that if the pc is down when an occurrence of the scheduling happens the process doesn't refresh the NextRunOn field in the scheduledJobs table so that the job stops until when the user manually stops and restarts all jobs.

Do you think is possible to modify something to fix this?

Sqlfreak
Starting Member

5 Posts

Posted - 2009-09-19 : 08:25:39

hi,
i am new to MS SQl.
firstly, thnx for the code. u solved a big hurdle for me.
Secondly, i have tried running the scheduled job, but it doesnt wrk.
i have MS SQL 2008. is that the problem?
Moreover, i tried using the schedule one time code from ur code, but it still doesnt run?
since my PC Has GMT+5:30, is tat the problem?
i am not getting any upadtes on my error table too.
and the field LastRunOn shows NULL?
coul you pls let me know what to do?
Also, in ur test code, u mentione a JOb Name as Test job and Step Name as Step?
whats the significance of this?
im calling a procedure i created to run at that point of time.
Pls do help me on this?

"live and let live..."

kacobp
Starting Member

2 Posts

Posted - 2010-01-21 : 11:03:17

Espero no lo tomen a mal, Soy nuevo en esto, necesito hacer algo parecido a esto y no entiendo muy bien el código aparte soy nulo con el ingles xD...

Por casualidad alguien tendrá los SP que se comentan en el artículo que me pudiera facilitar, realmente lo agradecería mucho.

• usp_AddJobSchedule
• usp_RemoveJobSchedule
• usp_AddScheduledJob
• usp_RemoveScheduledJob
• usp_AddScheduledJobStep
• usp_RemoveScheduledJobStep
• usp_StartScheduledJob
• usp_StopScheduledJob
• usp_RunScheduledJobSteps
• usp_RunScheduledJob

de antemano, muchas gracias!!!!!!!

GOOGLE TRANSLATION ...

I hope it does not take me wrong, I'm new to this, I need to do something like this and do not quite understand the code with separate English'm no xD ...

By chance someone will have the SP, as discussed in the article that would enable me, I really appreciate it.

• usp_AddJobSchedule
• usp_RemoveJobSchedule
• usp_AddScheduledJob
• usp_RemoveScheduledJob
• usp_AddScheduledJobStep
• usp_RemoveScheduledJobStep
• usp_StartScheduledJob
• usp_StopScheduledJob
• usp_RunScheduledJobSteps
• usp_RunScheduledJob

in advance, thank you very much !!!!!!!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2010-01-21 : 11:29:44

the whole source with stored procedures can be downloaded from the file accompanying the article.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.7 out!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources