Error Fix Database Mail XPs error in SQL Server

Error Fix Database Mail XPs error in SQL Server

Today I have received one email from one of the blog reader since he was facing an issue in his .NET application which is related to SMTP email in SQL Server. Here is the error he was facing:

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.

The statement has been terminated.

Here is the screen capture he sent to me:

EmailError

Well, by default, SMTP mail functionality comes disabled and if you intend to use it, you have to enable it with the help of SP_Configure system stored procedure.

Here is the small TSQL snippet which helps you to enable database mail functionality.

[sourcecode language=”sql”]

USE MASTER
GO

EXECUTE SP_CONFIGURE ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE ‘Database Mail XPs’, ‘1’
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE
GO
[/sourcecode]

Once you execute above given TSQL Snippet, you can use database mail functionality.

BTW, I have written few articles related to SP_Configure system stored procedure, if you are interested, have a look:

  • OpenRowSet and OpenQuery in SQL Server 2005/2008 (Click Here)
  • Enable XP_cmdshell in SQL Server 2008 (Click Here)
  • Copy files with SQL Server from one location to another location. (Click Here)
  • Enable “Ad Hoc Distributed queries” by sp_configure in SQL-Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

 

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “Error Fix Database Mail XPs error in SQL Server”

  1. Any ideas on what to do when you have run those commands, and you continue to get the exact same error in SQL 2012? I have been mashing my face against this problem for a solid two hours and can’t figure out what’s wrong. The configuration option “Database Mail XPs” is set to 1, and if I run my script by itself (outside of the SQL Agent Job) it runs fine. But when I run the SQL Agent job it freaks out with the error.

Comments are closed.