如何为查询分配参数以通过电子邮件发送输出
问题描述:
我试图将查询的结果作为电子邮件的正文发送......我想我非常接近......我决定使用2存储过程来完成此操作(第一个存储过程是一个示例电子邮件代码,因为它是SQL Server 2000,它没有内置的发送邮件,如2005年和2008年...如何为查询分配参数以通过电子邮件发送输出
这是我迄今为止存储。程序...你可以看到,我宣布我@Body然后分配给@Body存储过程getpaidout1929(Exec @Body = sp_getpaidout1929
),它运行查询:
SELECT Store_Id, Paid_Out_Amount, Paid_Out_Comment, Paid_Out_Datetime, Update_UserName
FROM Paid_Out_Tb
WHERE (Store_Id = 1929) AND (Paid_Out_Amount > 50) AND (Paid_Out_Datetime BETWEEN CONVERT(DATETIME, '2012-06-01 00:00:00', 102) AND CONVERT(DATETIME,
'2012-06-30 00:00:00', 102))
我然后执行该存储程序...
Exec sp_SQLNotify ‘[email protected]', ‘[email protected], ‘test’
它向我发送电子邮件,但有“0”作为机构留言...
AJ
Create PROCEDURE [dbo].[sp_SQLNotify]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" "
/*********************************************************************
This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
Declare @Body Varchar (8000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '10.0.1.3'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
Exec @Body = sp_getpaidout1929
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
PRINT 'Mail Sent!'
GO
答
你有没有执行存储过程检查syntax?如果你这样做:
exec @foo = dbo.SomeProc
然后@foo
包含的程序,在这种情况下是成功零的返回状态。
而且SQL 2000确实有built-in email,因此您可以将您的存储过程执行字符串作为@query
参数传递,以避免必须自己将结果集转换为标量值。
您还应该考虑像Perl,PowerShell等语言的外部脚本,因为使用纯TSQL方法格式化和电子邮件数据既不容易也不灵活。
我的SQL Server没有存储过程“XP_SendMail”,我还没有找到xp_sendmail SP ...这就是为什么我一直在使用上述...我想在C#中这样做,但没有当然,如果这样做会像SQL设置一样每天都会关闭一次。 – Shmewnix 2012-07-15 17:28:06