SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Scheduling Jobs in SQL Server Express - Part 2
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/01/2008 :  08:14:37  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

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 - 01/07/2009 :  07:06:28  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 01/07/2009 :  07:09:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

manishkaushik
Starting Member

14 Posts

Posted - 01/07/2009 :  07:33:18  Show Profile  Reply with Quote

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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 01/07/2009 :  09:26:38  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

manishkaushik
Starting Member

14 Posts

Posted - 01/07/2009 :  10:11:26  Show Profile  Reply with Quote
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
Go to Top of Page

manishkaushik
Starting Member

14 Posts

Posted - 01/07/2009 :  23:08:56  Show Profile  Reply with Quote
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
Go to Top of Page

manishkaushik
Starting Member

14 Posts

Posted - 01/09/2009 :  07:48:29  Show Profile  Reply with Quote
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
Go to Top of Page

vermeer_paul
Starting Member

New Zealand
2 Posts

Posted - 01/13/2009 :  22:03:08  Show Profile  Reply with Quote
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.
Go to Top of Page

manishkaushik
Starting Member

14 Posts

Posted - 01/13/2009 :  23:34:14  Show Profile  Reply with Quote
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
Go to Top of Page

vermeer_paul
Starting Member

New Zealand
2 Posts

Posted - 01/15/2009 :  15:05:47  Show Profile  Reply with Quote
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


Go to Top of Page

manishkaushik
Starting Member

14 Posts

Posted - 01/16/2009 :  10:31:15  Show Profile  Reply with Quote
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
Go to Top of Page

dracdliw
Starting Member

1 Posts

Posted - 01/21/2009 :  14:48:15  Show Profile  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 01/21/2009 :  15:10:14  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

PatrickW
Starting Member

2 Posts

Posted - 03/14/2009 :  01:18:29  Show Profile  Reply with Quote
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.
Go to Top of Page

Tacola
Starting Member

USA
2 Posts

Posted - 03/16/2009 :  01:29:58  Show Profile  Reply with Quote
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.
Go to Top of Page

akan
Starting Member

1 Posts

Posted - 04/03/2009 :  11:22:10  Show Profile  Reply with Quote
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.
Go to Top of Page

mvandoni
Starting Member

Italy
12 Posts

Posted - 04/07/2009 :  11:15:18  Show Profile  Reply with Quote
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?
Go to Top of Page

Sqlfreak
Starting Member

India
5 Posts

Posted - 09/19/2009 :  08:25:39  Show Profile  Reply with Quote
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..."
Go to Top of Page

kacobp
Starting Member

Chile
2 Posts

Posted - 01/21/2010 :  11:03:17  Show Profile  Reply with Quote
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 !!!!!!!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 01/21/2010 :  11:29:44  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!

Edited by - spirit1 on 01/21/2010 11:30:00
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000