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
Previous Page
Author  Topic Next Topic
Page: of 2

kacobp
Starting Member

Chile
2 Posts

Posted - 01/21/2010 :  11:50:47  Show Profile  Reply with Quote
Pfffffffff, sin comentarios… jajajaja
Había buscado algún link de descarga y no lo encontré…xD

Muchas gracias por la ayuda y sobretodo por la rápida respuesta, ahora si lo encontré…

--------------------------------------------------------------------------------------------------

Google Translation ...

Pfffffffff, no comment ... jajajaja
He had sought a download link and could not find it ... xD

Thank you very much for the help and especially for the quick response, now if found ...
Go to Top of Page

PatrickW
Starting Member

2 Posts

Posted - 04/09/2010 :  21:30:39  Show Profile  Reply with Quote
My provider Network Solutions doesn't allow scheduling jobs or you can not perform db maintenance tasks using scheduled jobs. So even with SS Manager I cannot schedule a job. So don't use Network Solutions if you want to use SQL Server.
Go to Top of Page

sbrauen
Starting Member

1 Posts

Posted - 06/15/2011 :  10:09:15  Show Profile  Reply with Quote
Spirit1,

First, excellent articles and code; you've done some things in your procs that I've never seen before and I've learned a few new tricks from them as well.

Second, I've been trying to implement a new FunctionType that gets triggered every X minutes, where Function determines X. Right now I've got the event running the first time and then getting set for a second run, i.e. NextRunTime is defined and set correctly. However, the event never gets fired again.

Do you have any ideas on what may be causing this?

Thanks!!
Go to Top of Page

deepakvermaseo
Starting Member

India
6 Posts

Posted - 06/24/2011 :  04:50:54  Show Profile  Reply with Quote
hello I think you right

Deepak Verma
Go to Top of Page

deepakvermaseo
Starting Member

India
6 Posts

Posted - 06/24/2011 :  04:51:54  Show Profile  Reply with Quote
hello

Deepak Verma
Go to Top of Page

akan_u
Starting Member

1 Posts

Posted - 06/27/2011 :  09:10:09  Show Profile  Reply with Quote
Thanks abunch this works, but i have a st proc with xp_cmdshell which runs when triggered either by calling Exec sp_name, but if i call Exec sp_name using the scheduling process given it fails with proxy account credentials invalid
Go to Top of Page

rmccart2
Starting Member

USA
1 Posts

Posted - 08/17/2011 :  17:12:55  Show Profile  Reply with Quote
quote:
Originally posted by vermeer_paul

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






Paul,
I created a complete user interface taht is web based to implement this job scheduler and all that works great but I then had the same problem you discribe and have changed the startJob to include the @validfrom. This at least now update the queue with a proper nextrun instead of the 1/1/1900 stuff. The first run went great will see how it all works over the next few days.

I will thank you in advance for the direction to a fix.
Thanks, Ron
Go to Top of Page

TNoyce
Starting Member

USA
1 Posts

Posted - 09/12/2014 :  10:28:50  Show Profile  Reply with Quote
Excellent article. I have been experimenting with this code, and thus far things seem to make a lot of sense.

I know this is an old article, and I do not know if you're still monitoring the comments, but I thought I'd give this a shot..

I noted that there are no routines that perform updates to existing jobs, which I have created myself. I was having issues with schedule jobs running after I changed their next run date. For example, changed the next run time from 08:00 to 08:15 for the same day. When 8:15 came around, the job failed to run. I found that by stopping the job, making the change, and restarting it that it executed at the new time.

Another thing I noticed is that the code which calculates next run time does not quite work the way I would expect. For example, if I create a new daily job which is supposed to run every day, and I specify a ValidFrom which is after the current time (future date), but in the same 24-hour time-frame, the next run time is set to 24 hours past the time I specify.

Example:
New job, with ValidFrom set to 9/12/2014 07:15, daily execution every day. Current date is 09/12/2014 7:00. After adding this job, the next run time is 09/13/2014 07:15 -- 24 hours after the time I specified.

This makes sense when the routine is called after the job actually executes, as it would be 24 hours in the future at that time.

I would like to be able to change the schedule and have it run on the same day if the specified time and date fall within the current 24 hour period. I can set the ValidFrom 24 hours before (in this case, 9/11/2014 07:15) and that works since it calculates NextRunOn as 09/12/2014 07:15, however that is a bit counter-intuitive.

Am I missing something?
Go to Top of Page
Page: of 2  Topic Next Topic  
Previous 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.08 seconds. Powered By: Snitz Forums 2000