Jobs
Sample code from Joe!
BEGIN TRANSACTION
DECLARE @last_step_number INT
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
SET @last_step_number = 4
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_name = 'promo_reports', @step_name=N'report_promo_sms',
@step_id=7,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=2,
@on_fail_action=2,
@on_fail_step_id=2,
@retry_attempts=2,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @body1 varchar(200)
set @body1 = ''Please find attached the weekly SMS report for "VOTE". Thanks''
EXEC msdb.dbo.sp_send_dbmail @recipients=''mail1@me.com;mail2@me.com'',
@subject = ''Promo VOTE Weekly SMS Report'',
@body = @body1,
@attach_query_result_as_file=1,
@query_attachment_filename=''messages.csv'',
@execute_query_database=''mydb'',
@exclude_query_output=1,
@query_result_header=0,
@query_result_separator='','',
@query =''set nocount on; EXEC report_promo_sms null, null, null''',
@database_name=N'mydb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_jobstep @job_name = 'promo_reports', @step_id = 6, @on_success_action=3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: