100分求助!!!怎样用SQL发MAIL时在邮件内容中加入查询结果表格？？？？？

PCBoy008 2004-12-30 11:12:59

例如，有 select * from sales
然后我希望在MAIL中内容：

您好，以下是本月销售统计：
【这里显示表格，而不是空格分隔的内容】

用xp_sendmail是否可行？？

...全文

163 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

passionke 2005-02-22

打赏
举报

constantine 2004-12-30

打赏
举报

vinsonshen 2004-12-30

打赏
举报

你要把查询结果以表格形式作为邮件内容？

这样SQL不能实现吧~~~

vinsonshen 2004-12-30

打赏
举报

关注~~~

iswear428 2004-12-30

打赏
举报

yjzhg 2004-12-30

打赏
举报

zjcxc 元老 2004-12-30

打赏
举报

要显示为表格的话,xp_sendmail不能直接实现

zlp321002 2004-12-30

打赏
举报

有办法让xp_sendmail发送HTML格式信件吗？

转贴，未测试，楼主搞定了，希望能写详细点贴上来：

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC SendHtmlMailTest(@vMailTo VARCHAR(255), @vMailFrom VARCHAR(255))
AS
BEGIN

SET CONCAT_NULL_YIELDS_NULL OFF

DECLARE
@Hresult INT,
@iMsg INT,
@iConf INT,
@ErrorSource VARCHAR (255),
@ErrorDesc VARCHAR (255),
@ServerIPAddr VARCHAR(15)

SET @ServerIPAddr = localhost --如本机是2000且启动了smtp服务

BEGIN

EXEC @Hresult = sp_OACreate 'CDO.Message', @iMsg OUT
IF @Hresult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @iMsg, @ErrorSource OUT, @ErrorDesc OUT

RAISERROR (@ErrorDesc, 16, 1, 1, 1, 2)

PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
RETURN
END

DECLARE @strBodyHTML VARCHAR(8000)

SET @strBodyHTML = @strBodyHTML + '<HTML><HEAD><basefont face=tahoma style="font-size: 9px;">'
SET @strBodyHTML = @strBodyHTML + '<STYLE>'
SET @strBodyHTML = @strBodyHTML + 'td.small {'
SET @strBodyHTML = @strBodyHTML + 'font-family:Verdana, Trebuchet, Arial;'
SET @strBodyHTML = @strBodyHTML + 'font-size:10px;'
SET @strBodyHTML = @strBodyHTML + 'color:#000000;'
SET @strBodyHTML = @strBodyHTML + 'text-align:left;'
SET @strBodyHTML = @strBodyHTML + 'line-height:12px;'
SET @strBodyHTML = @strBodyHTML + 'margin-top:0px;'
SET @strBodyHTML = @strBodyHTML + 'margin-bottom:0px;'
SET @strBodyHTML = @strBodyHTML + 'text-decoration: none;'
SET @strBodyHTML = @strBodyHTML + '}'
SET @strBodyHTML = @strBodyHTML + 'p.detail {font-family: monospace;'
SET @strBodyHTML = @strBodyHTML + 'font-size: 11px.; margin-left: 8;'
SET @strBodyHTML = @strBodyHTML + 'margin-top: 1;'
SET @strBodyHTML = @strBodyHTML + '}'
SET @strBodyHTML = @strBodyHTML + '</style></HEAD>'
SET @strBodyHTML = @strBodyHTML + '<BODY><font color=green><h3>HI! This is HMTL/SMTP mail.</h3></font><img src="http://www.csdn.net/images/ad/ad-java.gif" border=no><BR>'
SET @strBodyHTML = @strBodyHTML + ' <Font Face=Arial Size=5><B>This uses CDOSys and not outlook profiles!</B><BR>'
SET @strBodyHTML = @strBodyHTML + '<B>Set </B> <FONT COLOR=RED>Font Color</FONT></Font><BR><BR>'
SET @strBodyHTML = @strBodyHTML + '<HR /></BODY>'
SET @strBodyHTML = @strBodyHTML + '</HTML>'

/*Some info needed*/
DECLARE @cdoSendUsingPort INT
DECLARE @cdoAnonymous INT
SET @cdoSendUsingPort = 25
SET @cdoAnonymous = 0
EXEC @Hresult = sp_OASetProperty @iMsg, 'configuration("http://schemas.microsoft.com/cdo/configuration/sendusing")', @cdoSendUsingPort
EXEC @Hresult = sp_OASetProperty @iMsg, 'configuration("http://schemas.microsoft.com/cdo/configuration/smtpserver")', @ServerIPAddr
EXEC @Hresult = sp_OASetProperty @iMsg, 'configuration("http://schemas.microsoft.com/cdo/configuration/smtpserverport")', 25
EXEC @Hresult = sp_OASetProperty @iMsg, 'configuration("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")', @cdoAnonymous

IF @Hresult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @iMsg, @ErrorSource OUT, @ErrorDesc OUT
PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
RETURN
END

EXEC @Hresult = sp_OASetProperty @iMsg, 'To', @vMailTo
EXEC @Hresult = sp_OASetProperty @iMsg, 'From', 'MSSQL Notifications'
EXEC @Hresult = sp_OASetProperty @iMsg, 'Subject', 'HI! This is HMTL/SMTP mail.'
EXEC @Hresult = sp_OASetProperty @iMsg, 'Sender', @vMailFrom

set @strBodyHTML = replace(replace(@strBodyHTML, '''''', ''''), '''','''''')
EXEC @Hresult = sp_OASetProperty @iMsg, 'HTMLBody', @strBodyHTML
IF @Hresult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @iMsg, @ErrorSource OUT, @ErrorDesc OUT
PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
RETURN
END

/*Now send the message*/
EXEC @Hresult = sp_OAMethod @iMsg, 'Send'
IF @Hresult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @iMsg, @ErrorSource OUT, @ErrorDesc OUT
PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
RETURN
END

/*Cleanup*/
EXEC @Hresult = sp_OADestroy @iMsg
END
END