| Author |
 Topic  |
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
manishkaushik
Starting Member
14 Posts |
 Posted - 01/07/2009 : 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
Slovenia
11741 Posts |
Posted - 01/07/2009 : 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 - 01/07/2009 : 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
Slovenia
11741 Posts |
Posted - 01/07/2009 : 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 - 01/07/2009 : 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 - 01/07/2009 : 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 - 01/09/2009 : 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
New Zealand
2 Posts |
Posted - 01/13/2009 : 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 - 01/13/2009 : 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
New Zealand
2 Posts |
Posted - 01/15/2009 : 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 - 01/16/2009 : 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 Posts |
Posted - 01/21/2009 : 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
Slovenia
11741 Posts |
Posted - 01/21/2009 : 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 - 03/14/2009 : 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
USA
2 Posts |
Posted - 03/16/2009 : 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 Posts |
Posted - 04/03/2009 : 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
Italy
12 Posts |
Posted - 04/07/2009 : 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
India
5 Posts |
Posted - 09/19/2009 : 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
Chile
2 Posts |
Posted - 01/21/2010 : 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
Slovenia
11741 Posts |
Posted - 01/21/2010 : 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! |
Edited by - spirit1 on 01/21/2010 11:30:00 |
|
|
|
Topic |
|
Article: Scheduling Jobs in SQL Server Express - Part 2
