Sql-server – Sending SMTP mail in SQL Server 2008 R2 Express

sql-server

I tried using : 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.

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

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.

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?

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

Best Solution

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

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

  1. sysmail_account -> create a default mail account
  2. sysmail_profile -> create a default profile(you will need this with sp_send_dbmail)
  3. sysmail_profileaccount -> add related data to this based on 2 profile id
  4. 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.

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.

Talley Ouro Raleigh talleyouro@hotmail.com