sqlserver2000利用存储过程发邮件
业务逻辑alter proc exec_sendmail1@appname varchar(100), --申请人@guid varchar(100), --表单guid@id varchar(100), --表单编号@title varchar(500) --邮件标题asdeclare @from varchar(500)--发件箱declare @smtpserver varchar(200) --smtp服务器declare @sendusername varchar(200) --发送认证:用户名declare @sendpassword varchar(200) --发送认证:密码 declare @content varchar(2000) --邮件内容select @from = 'OA_SANYO@sanyo.com'select @smtpserver = '10.25.21.51'select @sendusername = 'OA_SANYO@sanyo.com'select @sendpassword = '20369845'select @content = @appname+'提交的'+@title+'('+@id+')'+'，审核不通过，已回退，请知晓！'--判断是否为空if @appname = '' or@guid = '' or @id = '' or @title = ''begin raiserror 50000 'please set the @appname and@guid and @id and @title values before excute the stored procedure' return -1end--动态设定查询行数declare @count intset @count = ((select count(*) from sys_inst_prcs where taskid = @guid and prc_id >= ( select top 1 prc_id from sys_inst_prcs where selactname = 0 and taskid = @guid and procuser is not null order by prc_id desc ))-1)print @count--创建临时表create table #temp(name varchar(50))--申明游标往临时表中插值set rowcount @count--限制循环次数为countdeclare tempCursor cursor forselect procuser from sys_inst_prcs where taskid = @guid and prc_id >= ( select top 1 prc_id from sys_inst_prcs where selactname = 0 and taskid = @guid and procuser is not null order by prc_id desc ) open tempCursordeclare @name varchar(50) --收件人fetch next from tempCursor into @namewhile @@fetch_status=0begin--向临时表中插数据insert into #temp(name) values(@name)fetch next from tempCursor into @nameendclose tempCursordeallocate tempCursor--声明游标发邮件declare sendMailCursor cursorforselect distinct(name) from #temp open sendMailCursordeclare @receiptant varchar(50) --收件人declare @to varchar(500) --收件箱fetch next from sendMailCursor into @receiptantwhile @@fetch_status=0begin--调用存储过程send_mail发邮件set @to = (select email from sys_user where username = @receiptant)exec send_mail @from=@from, @to = @to,@smtpserver = @smtpserver,@sendusername = @sendusername,@sendpassword = @sendpassword,@subject = @title,@body = @contentfetch next from sendMailCursor into @receiptantendclose sendMailCursordeallocate sendMailCursorgoexec exec_sendmail1 '陆宁','dad42bed-73c3-48aa-ab06-7962b335f0ff','NJSQ11060805383','年假申请表单'执行过程：当审批不同意时，查询出前面审批过的人的邮箱，利用游标循环查询结果，调用存储过程发送邮件给已经审批过的人，通知申请表单已经回退
参考链接：
http://chenxing.blog.51cto.com/240526/44621
执行发邮件
ALTER PROCEDURE . @From varchar(1000) ='',--发件人 @To varchar(1000) , --收件人 @smtpserver varchar(200),--smtp服务器 @sendusername varchar(200),--发送认证:用户名 @sendpassword varchar(200),--发送认证:密码 @Subject nvarchar(128)='', --标题 @Body nvarchar(4000) ='' --正文 with encryption /********************************************************************* This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. References to the CDOSYS objects are at the following MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp ***********************************************************************/ AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000) if @sendusername='' or@sendpassword='' begin raiserror 50000 'please set the @sendusername and@sendpassword values before excute the stored procedure' return -1 end --replace the quotation marks set @Subject=replace(@Subject,'''','''''') set @Body=replace(@Body,'''','''''') --************* Create the CDO.Message Object ************************ EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT --***************Configuring the Message Object ****************** -- This is to configure a remote SMTP server. -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' -- This is to configure the Server Name or IP address. -- Replace MailServerName by the name or IP of your SMTP Server. EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtpserver --这个需要注意：如果不将其值设为0，会报错，不知道为什么 EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','0' EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', @sendusername EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @sendpassword -- Save the configurations to the message object. EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null -- Set the e-mail parameters. EXEC @hr = sp_OASetProperty @iMsg, 'To', @To EXEC @hr = sp_OASetProperty @iMsg, 'From', @From EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body --选择了HTMLBody格式，就必须设置HTMLBodyPart，否则会出现乱码EXEC @hr = sp_OASetProperty @IMsg, 'HTMLBodyPart.Charset', 'gb2312'EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL if @@error<>0 or @hr<>0 begin raiserror 55000 '<send_mail> Error: send mail failed.' end else begin print 'Success: send mail ok.' end EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = '<send_mail> Error Source: ' + @source PRINT@output SELECT @output = '<send_mail> Error Description: ' + @description PRINT@output END ELSE BEGIN PRINT 'sp_OAGetErrorInfo failed.' RETURN END -- Do some error handling after each step if you have to. -- Clean up the objects created. EXEC @hr = sp_OADestroy @iMsg GO 参考链接：
http://topic.csdn.net/u/20100322/15/59f780a9-1a7a-4974-a830-7d80d3e90648.html
http://www.cnblogs.com/NeoLee/archive/2005/03/28/127316.html
页:
[1]