Job Alerts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Job Alerts

Author

Topic

newdba
Starting Member

30 Posts

Posted - 2011-06-13 : 11:25:50

We have this job created which we have to manually run. How do i set this up to run automatically and also send an email out when it is started and completed

USE [msdb]
GO

/****** Object: Job [EZPay Emails] Script Date: 06/13/2011 11:24:50 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 06/13/2011 11:24:50 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'EZPay Emails',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'MUSICARTS\timh',
@notify_email_operator_name=N'Tim Hicks', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Execute sp_EZPay_Email] Script Date: 06/13/2011 11:24:50 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute sp_EZPay_Email',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXECUTE [ITStorage].[dbo].[sp_EZPay_Email] ',
@database_name=N'ITStorage',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'One Time',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100723,
@active_end_date=99991231,
@active_start_time=124900,
@active_end_time=235959,
@schedule_uid=N'33f34a85-2a8d-49e1-a7f1-62537cd71538'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-13 : 11:54:59

Try changing @enabled to 1 on the schedule.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

newdba
Starting Member

30 Posts

Posted - 2011-06-13 : 13:21:53

ok so that will run it automatically?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-13 : 13:52:21

Should do - depends what the frequencey is set to. Have a look at it using the agent and test.
To send an email when it starts and completes I would add email steps and also one to execute on failure.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

newdba
Starting Member

30 Posts

Posted - 2011-06-13 : 14:59:42

can you help me with the steps? how should i incorporate them?

newdba
Starting Member

30 Posts

Posted - 2011-06-14 : 09:05:01

I have never used SQL Server Agent before I have been searching online...do you have a site that you recommend

newdba
Starting Member

30 Posts

Posted - 2011-06-14 : 13:39:24

i got it thanks