--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)
--Automatic email 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'
--Here is the recipient address. This variable seems useless, so add it directly at the end. Just look below-------
Set @recipient =''
--title----------------
Set @subject='Mail'
-------Here is the data you want to displayBegin---------------------
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
-------Here is the data you want to display 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 --Your recipient's email address--------------------------------------------- ---------------
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
--Automatic email begin
Exec @hr = sp_OAMethod @object, 'Send', null,@serveraddress
Exec @hr = sp_OAGetErrorInfo @object, @source OUTPUT, @description OUTPUT
if (@hr = 0)
Begin
Set @output='Error source: '+@source
Print@output
Select @output = 'Error description: ' + @description
Print@output
End
Else
Begin
Print 'Failed to obtain error information! '
Return
End
--Release JMail.Message object
EXEC @hr = sp_OADestroy @object
--IF @hr <> 0
--BEGIN
-- EXEC sp_OAGetErrorInfo @object
-- RETURN
--END
end
The above stored procedure is completed,
Attached is opening Ole Automation Procedures
---Open Ole Automation Procedures----
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
You can use it after making slight modifications based on your own data. Sending emails is generally used together with the sql server agent job to send emails regularly.
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/liaolian9948/archive/2009/12/31/5110313.aspx
-