sql server 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 @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
--@recipient--你的收件人邮箱地址---------------------------------------------------
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=錯誤源: +@source
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的方法。
说明，发送邮件一般与sql server agent 作业一起用，实现定时发送邮件。