在SQL Server 2008 R2 Express中发送SMTP邮件

I tried using : sp_send_dbmail

我尝试使用：sp_send_dbmail

But I got the following error:

但我收到以下错误：

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

消息15281，级别16，状态1，过程sp_send_dbmail，行0 SQL Server阻止访问组件“Database Mail XPs”的过程“dbo.sp_send_dbmail”，因为此组件已作为此服务器的安全性配置的一部分关闭。系统管理员可以使用sp_configure启用“Database Mail XPs”。有关启用“数据库邮件XP”的详细信息，请参阅SQL Server联机丛书中的“表面区域配置”。

I also tried to use this code to send SMTP mail in SQL Server 2008 R2 EXPRESS: http://www.freevbcode.com/ShowCode.asp?ID=6699

我还尝试使用此代码在SQL Server 2008 R2 EXPRESS中发送SMTP邮件：http：//www.freevbcode.com/ShowCode.asp？ID = 6699

But I am getting the following error:

但我收到以下错误：

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

消息15281，级别16，状态1，过程sp_OACreate，第1行SQL Server阻止访问组件“Ole Automation Procedures”的过程“sys.sp_OACreate”，因为此组件作为此服务器的安全性配置的一部分被关闭。系统管理员可以使用sp_configure启用“Ole Automation Procedures”。有关启用“Ole Automation Procedures”的详细信息，请参阅SQL Server联机丛书中的“Surface Area Configuration”。

I went to the "Facets" to check the security options there, but there is nothing about "Surface Area Configuration"! Is it missing because I am using the Express version of the SQL Server 2008 R2? Or am I going in the wrong direction?

我去了“Facets”检查那里的安全选项，但没有关于“表面区域配置”的内容！是不是因为我使用的是SQL Server 2008 R2的Express版本？还是我朝错误的方向走？

If you have any better code/suggestion for sending mail in SQL Server 2008, please let me know. Thanks!

如果您有任何更好的代码/建议在SQL Server 2008中发送邮件，请告诉我。谢谢！

5 个解决方案

#1

Phase 1: right click on sql server 2008r2 express within ssms/choose facets/choose Surface Area Configuration/set DatabaseMailEnabled ->true/click ok.Restart the server

第1阶段：右键单击ssms上的sql server 2008r2 express /选择facets /选择Surface Area Configuration /设置DatabaseMailEnabled - > true / click ok.Restart服务器

Phase2: You just need to configure some tables within msdb.Here are the tables that need to be configured:

阶段2：您只需要在msdb中配置一些表。这些是需要配置的表：

sysmail_account -> create a default mail account
sysmail_account - >创建默认邮件帐户
sysmail_profile -> create a default profile(you will need this with sp_send_dbmail)
sysmail_profile - >创建一个默认配置文件（你需要使用sp_send_dbmail）
sysmail_profileaccount -> add related data to this based on 2 profile id
sysmail_profileaccount - >根据2个配置文件ID向此添加相关数据
sysmail_server -> create a mail server from your email account you will be using to send emails.If you do not know the server type look inside sysmail_servertype.
sysmail_server - >从您将用于发送电子邮件的电子邮件帐户创建邮件服务器。如果您不知道服务器类型，请查看sysmail_servertype内部。

After updating these table refresh msdb and try sending email using sp_send_dbmail If you followed all these steps you will be able to send email within sql 2008 r2 express using sp_send_dbmail. I did 5 tests and it went well.

更新这些表刷新msdb并尝试使用sp_send_dbmail发送电子邮件如果您执行了所有这些步骤，您将能够使用sp_send_dbmail在sql 2008 r2 express中发送电子邮件。我做了5次测试，但进展顺利。

Talley Ouro Raleigh talleyouro@hotmail.com

#2

It turned out that the SQL Server 2008 R2 EXPRESS edition has no mail feature supported.

事实证明，SQL Server 2008 R2 EXPRESS版本没有支持邮件功能。

#3

With some credit to Tanmaya Thopate from this site, here is something that works in SQL Express on Windows Server 2008:

有了这个网站Tanmaya Thopate的一些功劳，这里有一些适用于Windows Server 2008上的SQL Express：

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyMail',
@description = 'Mail account for Database Mail',
@email_address = 'sender@domain.com',
@display_name = 'Me',
@username='myaccount@gmail.com',
@password='Password',
@mailserver_name = 'smtp.gmail.com'

Mail will be send through gmail with your account myaccount@gmail.com

邮件将通过gmail与您的帐户myaccount@gmail.com一起发送

Now create a profile:

现在创建个人资料：

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MyMailProfile',
@description = 'Profile needed for database mail'

Link the profile to the mail

将配置文件链接到邮件

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyMailProfile',
@account_name = 'MyMail',
@sequence_number = 1


EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MyMailProfile',
@principal_name = 'public',
@is_default = 1 ;

Ensure SSL is enable, otherwise Gmail will complain.

确保启用SSL，否则Gmail会抱怨。

use msdb
UPDATE sysmail_server 
SET enable_ssl = 1

And send a mail with:

并发送邮件：

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' Test DB Email SSL '
EXEC msdb.dbo.sp_send_dbmail @recipients='me@mywork.co.za',
@subject = 'Test',
@body = @body1,
@body_format = 'HTML' ;

You can view the logs from:

您可以从以下位置查看日志：

SELECT * FROM msdb.dbo.sysmail_event_log order by log_date

#4

I know the question is for express, but for the record here is what to do for proper sql server (SQL Server 2008 R2):

我知道问题是为了表达，但是这里的记录是如何为正确的SQL服务器（SQL Server 2008 R2）做的：

http://www.mssqltips.com/tip.asp?tip=1673

Connect to the server in management studio
连接到管理工作室中的服务器
Right click the server
右键单击服务器
Click "Facets"
点击“Facets”
In the Facet dropdown, select "Surface Area Configuration"
在Facet下拉列表中，选择“Surface Area Configuration”
Double-click the "DatabaseMailEnabled" row to set it to "True".
双击“DatabaseMailEnabled”行将其设置为“True”。

#5

Please execute the following script:

请执行以下脚本：

sp_configure 'show advanced options', 1 
GO 
RECONFIGURE; 
GO 
sp_configure 'Ole Automation Procedures', 1 
GO 
RECONFIGURE; 
GO 
sp_configure 'show advanced options', 1 
GO 
RECONFIGURE;

#1