代码语言
.
CSharp
.
JS
Java
Asp.Net
C
MSSQL
PHP
Css
PLSQL
Python
Shell
EBS
ASP
Perl
ObjC
VB.Net
VBS
MYSQL
GO
Delphi
AS
DB2
Domino
Rails
ActionScript
Scala
代码分类
文件
系统
字符串
数据库
网络相关
图形/GUI
多媒体
算法
游戏
Jquery
Extjs
Android
HTML5
菜单
网页交互
WinForm
控件
企业应用
安全与加密
脚本/批处理
开放平台
其它
【
MSSQL
】
SQL 2005 发送邮件 存储过程
作者:
/ 发布于
2010/12/31
/
614
GO
大家根据自己的数据稍作修改即可以用了,发送邮件一般与sql server agent 作业一起用,定时发送邮件
<div> -- exec P_TYAN_SERVICE_SITE_CHECK ALTER Procedure [dbo].[P_TYAN_SERVICE_SITE_CHECK] As Declare @count int, @object int, @hr int, @rc int, @output varchar(400), @description varchar (400), @source varchar(400), @sender varchar(50), @sendername varchar(50), @serveraddress varchar(255), @recipient varchar(255), @recipientName varchar(255), @subject varchar(255), @mailbody varchar(8000) --自动邮件 begin select @count= count(*) from [dbo].[TYAN_SERVICE_SITE] where area is not null and cust_name is not null and ship_location is not null and country is not null and cust_no is not null and (CUST_TYPE is null or SERVICE_SITE is null ) if(@count!=0) begin Set @sender='AutoMail' Set @sendername='AutoMail' Set @serveraddress='10.98.0.211' --这里是收件人地址,这个变量好像没用,直接在后面加。就看下面------- Set @recipient ='' --标题---------------- Set @subject='Mail' -------这里为你要显示的数据 Begin--------------------- Set @mailbody=N'<html><head> </head><body> <p style="margin-bottom:12.0pt"><font size=1 face=Arial><span lang=EN-US style="font-size:9.0pt;font-family:Arial">Dear Sir:<o:p></o:p></span></font> <p style="margin-bottom:12.0pt"><font size=1 face=Arial><span lang=EN-US style="font-size:9.0pt;font-family:Arial">Please maintain these new Tyan Service Site:<o:p></o:p></span></font> <table style="font-family:trebuchet ms;font-size: 10pt; border-collapse:collapse" cellpadding="2" bordercolor="black" border=1> <tr bgcolor=orange align=center> <td >AREA</td> <td >COUNTRY</td> <td >CUST NO</td> <td >CUST NAME</td> </tr>' --<td align=left>Dept Name</td> Declare @str varchar(8000), @AREA nvarchar(50), @COUNTRY varchar(50), @CUST_NO varchar(50), @CUST_NAME varchar(50), @vcount int begin set @vcount=1 set @str='' Declare c_at Cursor FOR select distinct area,country,cust_no,cust_name from [dbo].[TYAN_SERVICE_SITE] where area is not null and cust_name is not null and ship_location is not null and country is not null and cust_no is not null and (CUST_TYPE is null or SERVICE_SITE is null ) OPEN c_at FETCH NEXT FROM c_at INTO @AREA,@COUNTRY,@CUST_NO,@CUST_NAME WHILE (@@FETCH_STATUS = 0) BEGIN set @str=@str+'<tr align=center>' set @str=@str+'<td >'+@AREA+'</td>' set @str=@str+'<td >'+@COUNTRY+'</td>' set @str=@str+'<td >'+@CUST_NO+'</td>' set @str=@str+'<td >'+@CUST_NAME+'</td>' set @vcount=@vcount+1 FETCH NEXT FROM c_at INTO @AREA,@COUNTRY,@CUST_NO,@CUST_NAME END -------这里为你要显示的数据 end--------------------- CLOSE c_at DEALLOCATE c_at end Set @mailbody = @mailbody+@str+'</table></body></html>' Exec @hr = sp_OACreate 'jmail.message', @object OUTPUT Exec @hr = sp_OASetProperty @object, 'Charset', 'BIG5' <a href="mailto:--@recipient">--@recipient</a>--你的收件人邮箱地址--------------------------------------------------- Exec @hr = sp_OAMethod @object, 'AddRecipient',NULL,'your mailaddress' --Exec @hr = sp_OAMethod @object, 'AddRecipientBCC',NULL,'your mailaddress' Exec @hr = sp_OASetProperty @object, 'Subject',@subject Exec @hr = sp_OAsetProperty @object, 'HTMLBody' ,@mailbody Exec @hr = sp_OAsetProperty @object, 'Priority' , 1 If Not @sender is null Exec @hr = sp_OASetProperty @object, 'From', @sender If Not @sendername is null Exec @hr = sp_OASetProperty @object, 'FromName', @sendername --自动邮件 begin Exec @hr = sp_OAMethod @object, 'Send', null,@serveraddress Exec @hr = sp_OAGetErrorInfo @object, @source OUTPUT, @description OUTPUT if (@hr = 0) Begin Set @output='錯誤源: <a href="mailto:'+@source">'+@source</a> Print @output Select @output = '錯誤描述: ' + @description Print @output End Else Begin Print '獲取錯誤資訊失敗!' Return End --釋放JMail.Message對象 EXEC @hr = sp_OADestroy @object --IF @hr <> 0 --BEGIN -- EXEC sp_OAGetErrorInfo @object -- RETURN --END end 上面存储过程完成、 附 开启Ole Automation Procedures --- 开启Ole Automation Procedures---- sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO </div>
试试其它关键字
发送邮件
同语言下
.
SQL查询 多列合并成一行用逗号隔开
.
查看存储过程修改时间,最近执行时间
.
设置手动批量删除数据库相关进程
.
获取某个表中特定字段的所有字符串形式
.
SQL 如何去除重复的字符串
.
怎么去掉一个字段中的重复数据
.
String 去除空格 回车 换行 水平制表符
.
SQL查询和替换含有回车,空格,TAB
.
SQL SERVER 查询每日新增用户数量、次留数量
.
判断两个字符串是否存在相同的内容
可能有用的
.
C#实现的html内容截取
.
List 切割成几份 工具类
.
SQL查询 多列合并成一行用逗号隔开
.
一行一行读取txt的内容
.
C#动态修改文件夹名称(FSO实现,不移动文件)
.
c# 移动文件或文件夹
.
c#图片添加水印
.
Java PDF转换成图片并输出给前台展示
.
网站后台修改图片尺寸代码
.
处理大图片在缩略图时的展示
贡献的其它代码
Label
Copyright © 2004 - 2024 dezai.cn. All Rights Reserved
站长博客
粤ICP备13059550号-3