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:
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)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.