邮件发送作业调度 创建操作员
USE [msdb]
GO
--创建操作员7*24值班
/****** 对象: Operator [qiu_zhengqiang] 脚本日期: 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 的数据类型为 sysname，无默认值。
@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, --从星期日到星期六 1+2+4+8+16+32+64
@email_address =N'qiuzhengqiang@qq.com'
go
--创建邮件发送作业调度
--需要启动数据库对应实例的 SQL Server Agent服务
/****** 对象: Job [WorkLog_MailNotice] 脚本日期: 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' 每周五：给未完成本周工时填报的项目经理发送本周工时填报情况的邮件。调用WorkLogNotice存储过程。',
@notify_level_email =2, --失败后
@start_step_id = 1,
@notify_email_operator_name =N'qiu_zhengqiang' , @job_id = @jobId OUTPUT -- 作业失败，发送邮件。需创建对应操作员
IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 对象: Step [SendMail] 脚本日期: 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, --失败后退出
@retry_attempts =1,
@subsystem =N'TSQL' ,
@command =N'EXEC WorkLogNotice' , -- 需创建对应的存储过程
@database_name =N'WorkLog20120419' , -- 数据库名字对应
@flags =16 -- 将日志写入表中（追加到现有历史记录）
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, --每周
@freq_interval =32, --周五
@freq_subday_type =1, --在指定时间
@freq_recurrence_factor =1,
@active_start_date =20120608, --作业开始日期
@active_start_time =120000 -- 作业开始时间小时制 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:
发送邮件示例（游标）：
DECLARE @PName VARCHAR (300)
DECLARE @ChineseName VARCHAR (30)
DECLARE @E_Mail VARCHAR (100)
DECLARE @LeftHours FLOAT
DECLARE @Message VARCHAR (300)
DECLARE cMail CURSOR FOR
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+', 您好：<br> 您负责的项目 '+@PName +' 本周还有 '
+CONVERT( varchar,@LeftHours )+'工时未填报，请您抓紧时间填报。感谢您的支持！ ' + '</td></tr>'
+'<tr><td><br><br>MIS团队</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, -- 收件人地址
@subject = @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
