用于发送电子邮件的 SQL Server 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7112818/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Stored Procedure to Send Email
提问by B Woods
This is my first attempt at writing a stored procedure that emails someone. When trying to execute I get these errors:
这是我第一次尝试编写一个向某人发送电子邮件的存储过程。尝试执行时，出现以下错误：
Msg 102, Level 15, State 1, Procedure EmailTodaysErrors, Line 14
Incorrect syntax near '@MailServer'.
Msg 137, Level 15, State 2, Procedure EmailTodaysErrors, Line 26
Must declare the scalar variable "@mailserver".
Msg 137, Level 15, State 2, Procedure EmailTodaysErrors, Line 33
Must declare the scalar variable "@Body".
The code that I am using which is causing this is:
我正在使用的导致这种情况的代码是：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE EmailTodaysErrors
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000)
@MailServer varchar(100) = 'localhost'
AS
SET NOCOUNT ON;
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC sp_OADestroy @oMail
END
set nocount off
GO
Any help would be greatly appreciated. Thanks in advance.
任何帮助将不胜感激。提前致谢。
回答by Remus Rusanu
Don't use sp_oa_family to send mail, there is already a built-in solution with SQL Server: Database Mail. Simply configure Database Mail properly on your server, then call sp_send_dbmail.
不要使用 sp_oa_family 发送邮件，SQL Server 已经有内置的解决方案：Database Mail。只需在您的服务器上正确配置数据库邮件，然后调用sp_send_dbmail.
回答by JNK
You're missing a comma after the @bodyline, which is throwing off your declarations.
您在该@body行之后缺少一个逗号，这会丢弃您的声明。
Add it here:
在此处添加：
@Body varchar(8000), -- HERE
@MailServer varchar(100) = 'localhost'
回答by MaxiWheat
There's a comma missing in your parameters :
您的参数中缺少一个逗号：
@Body varchar(8000), ---- HERE
@MailServer varchar(100) = 'localhost'
回答by Kamal Pratap
SQL server code to send Mail through SQL DB Mail
通过 SQL DB Mail 发送邮件的 SQL 服务器代码
--Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestProfile',
@recipients = 'To Email Here',
@copy_recipients ='CC Email Here', --For CC Email if exists
@blind_copy_recipients= 'BCC Email Here', --For BCC Email if exists
@subject = 'Mail Subject Here',
@body = 'Mail Body Here',
@body_format='HTML',
@importance ='HIGH',
@file_attachments='C:\Test.pdf'; --For Attachments if existsFor more information how to set up DB mail refer the link
有关如何设置 DB 邮件的更多信息，请参阅链接
http://www.freshcodehub.com/Article/43/create-a-database-mail-configuration-using-t-sql-script
http://www.freshcodehub.com/Article/43/create-a-database-mail-configuration-using-t-sql-script
