SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


Configure SQL Mail XPs sys.xp_readmail by using sp_configure

SQL developers executing SQL Server SQL Mail XPs sys.xp_readmail can get the following error message if the sql database mail XPs is not configured or turned off by means of security. In such a case where SQL Mail xp_readmail component is not enabled, following T-SQL statement is enough to throw the below sql error message

EXEC xp_readmail
Code

Msg 15281, Level 16, State 1, Procedure xp_readmail, Line 1
SQL Server blocked access to procedure 'sys.xp_readmail' of component 'SQL 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 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.



SQL Server database administrators or SQL programmers who have enough authorizations on the SQL Server instance can configure SQL Mail and enable SQL Mail XPs by using the sp_configure system procedure as shown in the following steps.

First execute sp_configure sql procedure and check if advanced options are enabled or not.

EXEC sp_configure
Code

If the output of the sp_configure command shows a long list of SQL Server configuration items, or the "show advanced options" item has the run_value column with value 1 then you can skip the following step.
If "show advanced options" is 0, then SQL Server database administrator should switch the configuration options from 0 to 1 as follows.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
Code

After the above step is executed successfully where you got the information message "Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.", we can continue.
Run the "EXEC sp_configure" command once more.

enable SQL Mail XPs xp_readmail using sp_configure

As you see in the above screenshot which shows partially the output of the sp_configure SQL Server configuration values, the SQL Mail XPs has run_value equal to 0
Now in this step we'll modify the run value of SQL database mail XPs from 0 to 1 using the following sp_configure command.

EXEC sp_configure 'SQL Mail XPs', 1
GO
RECONFIGURE
GO
Code

If you got the following information message from SQL Engine "Configuration option 'SQL Mail XPs' changed from 0 to 1. Run the RECONFIGURE statement to install." this means you are successful to modify the SQL Server configuration values for database mail XPs.

Now you can revert back to original value of "show advanced options" configuration value and end the modification script here.

EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
Code

T-SQL developers can use the xp_readmail SQL Mail XPs in their sql scripts after this modification in the SQL Server configuration



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.