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.


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

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.

 

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

  1. Jacob Benson (@vhusker)

    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.