Use [MSDB]
Go
-- Create an operator on duty 7x24
/***** Object: operator [qiu_zhengqiang] script Date: 06/08/2012 12:58:42 ******/
-- Exec MSDB. DBO. sp_delete_operator @ name = n' Qiu _ zhengqiang'
Exec MSDB. DBO. sp_add_operator
@ Name = n' Qiu _ zhengqiang ', -- name of the operator (Notification recipient. The name must be unique and cannot contain percent (%) characters. The data type of name is sysname, with no default value.
@ Enabled = 1,
@ Weekday_pager_start_time = 80000,
@ Weekday_pager_end_time = 80000,
@ Saturday_pager_start_time = 80000,
@ Saturday_pager_end_time = 80000,
@ Sunday_pager_start_time = 80000,
@ Sunday_pager_end_time = 80000,
@ Pager_days = 127, -- from Sunday to Saturday 1 + 2 + 4 + 8 + 16 + 32 + 64
@ Email_address = n' qiuzhengqiang @ QQ.com'
Go
-- Create mail sending Job Scheduling
-- The SQL Server Agent service of the corresponding database instance needs to be started.
/***** Object: job [worklog_mailnotice] script Date: 06/08/2012 12:57:13 ******/
Begin transaction
Declare @ returncode int
Select @ returncode = 0
Declare @ jobid binary (16)
Exec @ returncode = MSDB. DBO. sp_add_job
@ Job_name = n' worklog _ mailnotice ',
@ Enabled = 1,
@ Description = n' every Friday: send an email to the project manager who has not completed completing this week's work report. Call the worklognotice stored procedure. ',
@ Policy_level_email = 2, -- after failure
@ Start_step_id = 1,
@ Policy_email_operator_name = n' Qiu _ zhengqiang ', @ job_id = @ jobid output -- the job fails and an email is sent. Corresponding operator needs to be created
If (@ error <> 0 or @ returncode <> 0) goto quitwithrollback
/***** Object: Step [Sendmail] script Date: 06/08/2012 12:57:13 ******/
Exec @ returncode = MSDB. DBO. sp_add_jobstep
@ Job_id = @ jobid,
@ Step_name = n 'sendmail ',
@ Step_id = 1,
@ On_fail_action = 2, -- exit after failure
@ Retry_attempts = 1,
@ Subsystem = n'tsql ',
@ Command = n' exec worklognotice ', -- the corresponding stored procedure needs to be created.
@ Database_name = n' worklog20120419 ', -- Database Name
@ Flags = 16 -- write logs to the table (append to existing history records)
If (@ error <> 0 or @ returncode <> 0) goto quitwithrollback
Exec @ returncode = MSDB. DBO. sp_add_jobschedule
@ Job_id = @ jobid,
@ Name = n' worklogmailnotice ',
@ Enabled = 1,
@ Freq_type = 8, -- weekly
@ Freq_interval = 32, -- Friday
@ Freq_subday_type = 1, -- at the specified time
@ Freq_recurrence_factor = 1,
@ Active_start_date = 20120608, -- job start date
@ Active_start_time = 120000 -- job start time in the hour format hhmmss
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:
Email sending example (cursor ):
Declare @ pname varchar (300)
Declare @ chinesename varchar (30)
Declare @ e_mail varchar (100)
Declare @ lefthours float
Declare @ message varchar (300)
Declare CMail cursor
Select projectname + '[' + projectcode + '] 'pname, chinesename, e_mail, esthours-realhours lefthours from # projectinfo
Open CMail
Fetch next from CMail into @ pname, @ chinesename, @ e_mail, @ lefthours
While @ fetch_status = 0
Begin
If @ lefthours> 0
Begin
Set @ message = '<HTML> <body> <tr> <TD>' + @ chinesename + ', hello: <br> the project you are in charge of '+ @ pname +' is available this week'
+ Convert (varchar, @ lefthours) + 'work hours are not filled in. Please wait for the time to fill in. Thank you for your support! '+' </TD> </tr>'
+ '<Tr> <TD> <br> MIS team </TD> </tr>'
+ '<Tr> <TD> <br>' + convert (varchar, getdate (), 120) + '</TD> </tr>'
+ '</Body> <HTML> ';
Exec MSDB. DBO. sp_send_dbmail
@ Profile_name = @ profile_name,
@ Recipients = @ e_mail, -- recipient address
@ Subject = @ subject, -- mail subject
@ Importance = @ importance,
@ Body = @ message,
@ Body_format = 'html ';
Print @ message
End
Fetch next from CMail into @ pname, @ chinesename, @ e_mail, @ lefthours
End
Close CMail
Deallocate CMail
End