ASP源码
PHP源码
.NET源码
JSP源码
-- 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'
ma***n-bottom:12.0pt">
lang=EN-US style="fo***size:9.0pt;font-family:Arial">Dear Sir:
ma***n-bottom:12.0pt">
lang=EN-US style="fo***size:9.0pt;font-family:Arial">Please maintain these new Tyan
Service Site:
AREA | COUNTRY | CUST NO | CUST NAME | Dept Name |
'+@AREA+' | ''+@COUNTRY+' | ''+@CUST_NO+' | ''+@CUST_NAME+' | '
--@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
--釋放JM***.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
-