ID #65768

SQL 2005 发送邮件 存储过程

-- 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>
<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></p>
<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 @[email protected]+'<tr align=center>'
set @[email protected]+'<td >'[email protected]+'</td>'
set @[email protected]+'<td >'[email protected]+'</td>'
set @[email protected]+'<td >'[email protected]_NO+'</td>'
set @[email protected]+'<td >'[email protected]_NAME+'</td>'
set @[email protected]+1
FETCH NEXT FROM c_at INTO @AREA,@COUNTRY,@CUST_NO,@CUST_NAME
  END

-------这里为你要显示的数据  end---------------------
CLOSE c_at
DEALLOCATE c_at
end
Set @mailbody = @[email protected]+'</table></body></html>'
Exec @hr = sp_OACreate 'jmail.message', @object OUTPUT
Exec @hr = sp_OASetProperty @object, 'Charset', 'BIG5'

[email protected]你的收件人邮箱地址---------------------------------------------------
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='錯誤源: '[email protected]
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

大家根据自己的数据稍作修改即可以用了,发送邮件一般与sql server agent 作业一起用,定时发送邮件。



本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/liaolian9948/archive/2009/12/31/5110313.aspx

2011-08-28 10:54
阅读:
I'm VC , Just U know Y
本站部分文章来源于互联网,版权归原作者所有。

延伸阅读:

SQL Server 数据存储与 NTFS 簇的大小

关于连接SQL Server 2000时提示“SQL Server不存在或拒绝访问”的处理

如何管理SQL Server的数据库对象

SQL SERVER 2000 连接不上的解决方法

Microsoft SQL Server 查询处理器的内部机制与结构