Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using sp_add_schedule Expand / Collapse
Author
Message
Posted Tuesday, March 3, 2009 8:49 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 6, 2012 7:03 AM
Points: 78, Visits: 168
Obviously, I've overlooked something. Any experts see it? I have a stored procedure to schedule a task to run every "RunsEvery" hours. It seems to work, but my "SendBookingReminders" procedure isn't exec'ing every RunsEvery hours.

ALTER PROCEDURE [dbo].[StartBookingReminders]
-- Add the parameters for the stored procedure here
@RunsEvery Int,
@ServerName VARCHAR(30),
@DatabaseName VARCHAR(128)

AS
BEGIN
Exec dbo.StopBookingReminders
exec msdb.dbo.sp_add_job @job_name = 'CS-Booking_Reminders',
@enabled=1
exec msdb.dbo.sp_add_jobstep
@job_name = 'CS-Booking_Reminders',
@step_name = 'Check for reminders to send',
@subsystem = 'TSQL',
@command = 'exec dbo.SendBookingReminders',
@database_name = @databaseName
exec msdb.dbo.sp_add_schedule
@schedule_name = 'CS-Booking_Reminders_Schedule',
@enabled = 1,
@freq_subday_type = 8,
@freq_type = 128,
@freq_subday_interval = @RunsEvery
exec msdb.dbo.sp_attach_schedule
@job_name = 'CS-Booking_Reminders',
@schedule_name = 'CS-Booking_Reminders_Schedule'


END


Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.
Post #667341
Posted Tuesday, March 3, 2009 9:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 10,191, Visits: 13,115
Is the job running at all?

What is returned if you run a query like this:

SELECT
*
FROM
msdb.dbo.sysjobhistory AS JH JOIN
msdb.dbo.sysjobs AS J
ON JH.job_id = J.job_id
WHERE
J.NAME = 'CS-Booking_Reminders'





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #667381
Posted Tuesday, March 3, 2009 3:21 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 6, 2012 7:03 AM
Points: 78, Visits: 168
Nope. The job isn't running. I don't find it listed in the sysjobhistory list, though it is in the sysjobs table.




Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.
Post #667700
Posted Wednesday, March 4, 2009 3:17 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 6, 2012 7:03 AM
Points: 78, Visits: 168
I tried adding msdb.dbo.sp_add_jobserver to my start procedure

ALTER PROCEDURE [dbo].[StartBookingReminders] 
-- Add the parameters for the stored procedure here
@RunsEvery Int,
@ServerName VARCHAR(30),
@DatabaseName VARCHAR(128)

AS
BEGIN
Exec dbo.StopBookingReminders
exec msdb.dbo.sp_add_job @job_name = 'CS-Booking_Reminders',
@enabled=1
exec msdb.dbo.sp_add_jobstep
@job_name = 'CS-Booking_Reminders',
@step_name = 'Check for reminders to send',
@subsystem = 'TSQL',
@command = 'exec dbo.SendBookingReminders',
@database_name = @databaseName
exec msdb.dbo.sp_add_schedule
@schedule_name = 'CS-Booking_Reminders_Schedule',
@enabled = 1,
@freq_subday_type = 8,
@freq_type = 128,
@freq_subday_interval = @RunsEvery
exec msdb.dbo.sp_attach_schedule
@job_name = 'CS-Booking_Reminders',
@schedule_name = 'CS-Booking_Reminders_Schedule'
exec msdb.dbo.sp_add_jobserver
@job_name = 'CS-Booking_Reminders',
@server_name = @ServerName

END

Haven't seen any results in the job history yet

SELECT
*
FROM
msdb.dbo.sysjobhistory AS JH JOIN
msdb.dbo.sysjobs AS J
ON JH.job_id = J.job_id
WHERE
J.NAME = 'CS-Booking_Reminders'



Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.
Post #668752
Posted Thursday, March 5, 2009 4:26 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 6, 2012 7:03 AM
Points: 78, Visits: 168
The End of the Matter:

ALTER PROCEDURE [dbo].[StartBookingReminders] 
-- Add the parameters for the stored procedure here
@RunsEvery Int,
@ServerName VARCHAR(30),
@DatabaseName VARCHAR(128)

AS
BEGIN
Exec dbo.StopBookingReminders
exec msdb.dbo.sp_add_job @job_name = 'CS-Booking_Reminders',
@enabled=1
exec msdb.dbo.sp_add_jobstep
@job_name = 'CS-Booking_Reminders',
@step_name = 'Check for reminders to send',
@subsystem = 'TSQL',
@command = 'exec dbo.SendBookingReminders',
@database_name = @databaseName
exec msdb.dbo.sp_add_schedule
@schedule_name = 'CS-Booking_Reminders_Schedule',
@enabled = 1,
@freq_interval = 1,
@freq_type = 4,
@freq_subday_type = 8,
@freq_recurrence_factor = 1,
@freq_subday_interval = @RunsEvery
exec msdb.dbo.sp_attach_schedule
@job_name = 'CS-Booking_Reminders',
@schedule_name = 'CS-Booking_Reminders_Schedule'
exec msdb.dbo.sp_add_jobserver
@job_name = 'CS-Booking_Reminders',
@server_name = @ServerName

END

The trick is in getting the Add Schedule call correct. To get something to happen each hour every day, you need to set:

@freq_interval = 1,
@freq_type = 4,
@freq_subday_type = 8,
@freq_recurrence_factor = 1,
@freq_subday_interval = @RunsEvery

The scheduler looks at the @freq_interval and @freq_type to decide which days to run it on, then, on those days, it looks at @freq_subday_type and @freq_subday_interval to decide when to run it on those days.

If your recurrence_faction is 0, the scheduler will run it the first time on the first day, and not again.


Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.
Post #669815
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse