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: