How to turn on Database Mail XP’s in SQL Server?

While executing a step in SQL server, there is a need to configure the ‘database mail feature’ and ‘Show advanced options’.

We can even encounter an error as shown below while executing a step in job with sp_send_dbmail and extended stored procedures.

Error Message:


“Executed as user: NT SERVICE\SQLSERVERAGENT. 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', search for 'Database Mail XPs' in SQL Server Books Online. [SQLSTATE 42000] (Error 15281).  The step failed.”

To enable the above error, please use the below command.


sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'Database Mail XPs', 1; 
GO 
RECONFIGURE 
GO

Further,

1. The Database Mail is currently not enabled when “0” in the command. This is the default option set in SQL server
Example: sp_configure 'Database Mail XPs', 0; 
 
2. The Database Mail can be enabled when “1” in the command

      Example: sp_configure 'Database Mail XPs', 1;

0 comments: