March 3, 2009 at 8:49 am
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
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
March 3, 2009 at 9:16 am
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
Consultant - Straight Path Solutions
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
March 3, 2009 at 3:21 pm
Nope. The job isn't running. I don't find it listed in the sysjobhistory list, though it is in the sysjobs table.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
March 4, 2009 at 3:17 pm
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'
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
March 5, 2009 at 4:26 pm
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.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply