Sysmail_configure_sp – Day 10 – SQL Server System Stored Procedure

Hi friends, from today we will discuss about database mail stored procedures. In next few days you will learn how to configure database mail, how to add mail accounts, how to add profile, and how to send mail.

Today, we will discuss about configuration setting for database mail using sql server system stored procedure sysmail_configure_sp.

Sysmail_configuration_sp stored procedure is used to change the configuration setting for database mail which applies to the entire SQL Server instance. This stored procedure is stored in msdb database and owned by schema dbo. If current database is not msdb then this stored procedure must be executed with three-part name. Permission to execute this stored procedure is default to sysadmin server role.

Syntax of sysmail_configure_sp is as follows:

sysmail_configure_sp [ [ @parameter_name = ] 'parameter_name' ]
    [ , [ @parameter_value = ] 'parameter_value' ]
    [ , [ @description = ] 'description' ]

Arguments

@parameter_name  is the name of the parameter to change.

@parameter_value is the new value of the parameter.

@description is the description of the parameter.

First we need to enable database mail service using following code –

use master
go
sp_configure 'show advanced options', 1
go
reconfigure with override
go
sp_configure 'Database Mail XPs', 1
go
reconfigure 
go

There are many options to configure and change database mail which are:

AccountRetryAttempts – The number of times the external mail process attempts to send the e-mail message using each account in specified profile. Default value is 1.

AccountRetryDelay – The number of times, in seconds, for the external mail process to wait between attempts to send a message. Default value is 5000.

DatabaseMailExeMinimumLifeTime – The minimum amount of time, in seconds, that the external mail process remains active. Default value is 600.

DefaultAttachmentEncoding – The default encoding for e-mail attachment. Default value is MIME.

MaxFileSize – The maximum size of an attachment in bytes. Default value is 1000000.

ProhibitedExtensions – A list of extensions which cannot be sent as an e-mail attachment to an e-mail message. Default value is exe,dll,vbs,js.

LoggingLevel – Specify which messages are recorded in Database mail log with possible value 1, 2, or 3. Default value is 2.

• 1 – This is normal mode. Logs only errors.
• 2 – This is extended mode. Logs errors, warnings, and informational messages.
• 3 – This is verbose mode. Logs errors, warnings, informational messages, success messages, and additional internal messages. Use this mode for troubleshooting.

Now let’s change the configuration value of Prohibited extension and add bat value in the list.

EXEC msdb.dbo.sysmail_configure_sp 'ProhibitedExtensions', 'exe,dll,vbs,js,bat';

After executing this procedure one more value gets added in the Prohibited Extension.

Similarly we can change the attachment size to 40000 –

EXEC msdb.dbo.sysmail_configure_sp 'MaxFileSize', '40000';

Now to see the changed values execute below stored procedure –

EXEC msdb.dbo.sysmail_help_configure_sp

We will continue with other database mail procedure in next blogs.

Hope you will like this post.

Regards,

Kapil Singh

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Follow me on Twitter