Five Undocumented SQL Server 2014 Database Mail Procedures
SQL Server 2014 supports the following useful undocumented database mail stored procedures:
- sp_RunMailQuery
- sp_SendMailMessage
- sp_sysmail_activate
- sysmail_verify_account_sp
- sysmail_verify_profile_sp
Note. These stored procedures exist in the msdb database, not in master.
sp_RunMailQuery
The sp_RunMailQuery database mail stored procedure is used to run the mail query. This
stored procedure is used in the sp_send_dbmail stored procedure to execute a query if
query is specified.
Syntax
sp_RunMailQuery [ @query = ] ‘query’,
[ @attach_results = ] ‘attach_results’,
[ @query_attachment_filename = ] ‘query_attachment_filename’,
[ @no_output = ] ‘no_output’,
[ @query_result_header = ] ‘query_result_header’,
[ @separator = ] ‘separator’,
[ @echo_error = ] ‘echo_error’,
[ @dbuse = ] ‘dbuse’,
[ @width = ] ‘width’,
[ @temp_table_uid = ] ‘temp_table_uid’,
[ @query_no_truncate = ] ‘query_no_truncate’,
[ @query_result_no_padding = ] ‘query_result_no_padding’
Arguments
[ @query = ] ‘query’
Is a query to execute. query is NVARCHAR(max), with no default.
[ @attach_results = ] ‘attach_results’
Specifies whether the result set of the query is returned as an attached file.
attach_results is bit, with no default.
1 – attach results, 0 – do not attach results.
[ @query_attachment_filename = ] ‘query_attachment_filename’
Specifies the file name to use for the result set of the query attachment.
query_attachment_filename is NVARCHAR(260), with a default of NULL.
[ @no_output = ] ‘no_output’
Specifies whether to return the output of the query execution in the e-mail message.
no_output is bit, with no default.
1 – without output, 0 – with output.
[ @query_result_header = ] ‘query_result_header’
Specifies whether the query results include column headers. query_result_header is bit,
with no default.
1 – query results contain column headers,
0 – query results do not include column headers.
[ @separator = ] ‘separator’
Is the character used to separate columns in the query output. separator is VARCHAR(1),
with no default.
[ @echo_error = ] ‘echo_error’
Specifies whether to send the e-mail when an error returns from the query specified in the
@query argument. echo_error is bit, with no default.
[ @dbuse = ] ‘dbuse’
Is the database context within which the stored procedure runs the query. dbuse is syname,
with no default.
[ @width = ] ‘width’
Is the line width, in characters, to use for formatting the results of the query.
width is INT, with no default.
[ @temp_table_uid = ] ‘temp_table_uid’
Is a temporary table identifier. temp_table_uid is uniqueidentifier, with no default.
[ @query_no_truncate = ] ‘query_no_truncate’
Specifies whether to execute the query with the option that avoids truncation of large
variable length data types (varchar(max), nvarchar(max), varbinary(max), xml, text,
ntext, image, and user-defined data types). query_no_truncate is bit, with no default.
0 – columns in the query will be truncated to 256 characters,
1 – columns in the query will not be truncated.
[ @query_result_no_padding = ] ‘query_result_no_padding’
This flag specifies would the query result be padding or no.
@query_result_no_padding is bit, with no default.
Return Code Values
int
Result Sets
None.
Remarks
The sp_RunMailQuery stored procedure returns:
2 – when the database mail is not permitted to send files with this file extension
101 – when the query results size was over the configured MaxFileSize.
This stored procedure exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to run the mail query:
DECLARE @uid uniqueidentifier
SET @uid = NEWID()
EXEC msdb.dbo.sp_RunMailQuery
@query = ‘SELECT * FROM AdventureWorks.Production.WorkOrder’,
@attach_results = 1,
@no_output = 1,
@query_result_header = 1,
@separator = ‘ ‘,
@echo_error = 1,
@dbuse = ‘AdventureWorks’,
@width = 255,
@temp_table_uid = @uid,
@query_no_truncate = 1,
@query_result_no_padding = 1
sp_SendMailMessage
The sp_SendMailMessage database mail stored procedure is used to send a request on the mail
items SSB (SQL Server Service Broker) queue.
Syntax
sp_SendMailMessage [ @contract_name = ] ‘contract_name’,
[ @message_type = ] ‘message_type’,
[ @request = ] ‘request’
Arguments
[ @contract_name = ] ‘contract_name’
The name of contract. contract_name is sysname, with no default.
[ @message_type = ] ‘message_type’
The type of message. message_type is sysname, with no default.
[ @request = ] ‘request’
The XML message to send. request is varchar(max), with no default.
Return Code Values
int
Result Sets
None.
Remarks
The sp_SendMailMessage stored procedure returns:
0 – when executed successful
error number = @@ERROR – when error exists
This stored procedure exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to write the message to the queue:
EXEC msdb.dbo.sp_SendMailMessage
@contract_name = ‘//www.microsoft.com/databasemail/contracts/SendMail/v1.0’,
@message_type = ‘{//www.microsoft.com/databasemail/messages}SendMail’,
@request = ‘Test message’
sp_sysmail_activate
The sp_sysmail_activate database mail stored procedure is used to start the DatabaseMail
process if it is not already running.
Syntax
sp_sysmail_activate
Return Code Values
int
Result Sets
None.
Remarks
The sp_sysmail_activate stored procedure returns 1 if failure and other value if success.
This stored procedure exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to start the DatabaseMail process if it is not already running:
EXEC msdb.dbo.sp_sysmail_activate
sysmail_verify_account_sp
The sysmail_verify_account_sp database mail stored procedure is used to verify the Database
Mail account. This stored procedure is used in many other database mail procedures such as
sysmail_help_account_sp, sysmail_update_account_sp, sysmail_delete_account_sp, and so on.
Syntax
sysmail_verify_account_sp [ @account_id = ] ‘account_id’,
[ @account_name = ] ‘account_name’,
[ @allow_both_nulls = ] ‘allow_both_nulls’,
[ @allow_id_name_mismatch = ] ‘allow_id_name_mismatch’,
[ @accountid = ] ‘accountid’
Arguments
[ @account_id = ] ‘account_id’
Is the account identifier. account_id is int, with no default.
[ @account_name = ] ‘account_name’
Is the name of the account. account_name is sysname, with no default.
[ @allow_both_nulls = ] ‘allow_both_nulls’
The flag indicates whether both account_id and account_name can be null or not.
allow_both_nulls is bit, with no default.
[ @allow_id_name_mismatch = ] ‘allow_id_name_mismatch’
The flag indicates whether the account_id and account_name can be mismatch or not.
allow_id_name_mismatch is bit, with no default.
[ @accountid = ] ‘accountid’
Is the account identifier from the sysmail_account system table. accountid is an output parameter.
accountid is int, with no default.
Return Code Values
int
Result Sets
None.
Remarks
The sysmail_verify_account_sp stored procedure returns:
0 – when executed successful
1 – when @allow_both_nulls = 0 and both @account_id and @account_name is null
2 – when @allow_id_name_mismatch = 0 and @account_id and @account_name do not match.
3 – when account id is not valid
4 – when account name is not valid
This stored procedure exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to verify the TestAccount database mail account:
DECLARE @accountid2 int
DECLARE @accountid int
EXEC @accountid2 = msdb.dbo.sysmail_verify_account_sp null, ‘TestAccount’, 1, 1, @accountid OUTPUT
SELECT @accountid2, @accountid
sysmail_verify_profile_sp
The sysmail_verify_profile_sp database mail stored procedure is used to verify the Database
Mail profile. This stored procedure is used in many other database mail procedures such as
sp_send_dbmail, sysmail_help_profile_sp, sysmail_update_profile_sp, sysmail_delete_profile_sp,
and so on.
Syntax
sysmail_verify_profile_sp [ @profile_id = ] ‘profile_id’,
[ @profile_name = ] ‘profile_name’,
[ @allow_both_nulls = ] ‘allow_both_nulls’,
[ @allow_id_name_mismatch = ] ‘allow_id_name_mismatch’,
[ @profileid = ] ‘profileid’
Arguments
[ @profile_id = ] ‘profile_id’
Is the profile identifier. profile_id is int, with no default.
[ @profile_name = ] ‘profile_name’
Is the name of the profile. profile_name is sysname, with no default.
[ @allow_both_nulls = ] ‘allow_both_nulls’
The flag indicates whether both profile_id and profile_name can be null or not.
allow_both_nulls is bit, with no default.
[ @allow_id_name_mismatch = ] ‘allow_id_name_mismatch’
The flag indicates whether the profile_id and profile_name can be mismatch or not.
allow_id_name_mismatch is bit, with no default.
[ @profileid = ] ‘profileid’
Is the profile identifier from the sysmail_profile system table. profileid is an output parameter.
profileid is int, with no default.
Return Code Values
int
Result Sets
None.
Remarks
The sysmail_verify_profile_sp stored procedure returns:
0 – when executed successful
1 – when @allow_both_nulls = 0 and both @profile_id and @profile_name is null
2 – when @allow_id_name_mismatch = 0 and @profile_id and @profile_name do not match.
3 – when profile id is not valid
4 – when profile name is not valid
This stored procedure exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to verify the TestProfile database mail profile:
DECLARE @profileid2 int
DECLARE @profileid int
EXEC @profileid2 = msdb.dbo.sysmail_verify_profile_sp null, ‘TestProfile’, 1, 1, @profileid OUTPUT
SELECT @profileid2, @profileid