With the advent of SQL Server 2005 and 2008, mail in the database engine has changed dramatically. Previously you had several options for working with email in the database. You could use any of the following procedures:
XP_SENDMAIL – Click here for details on this procedure
XP_READMAIL– Click here for details on this procedure
SP_PROCESSMAIL– Click here for details on this procedure
All of this functionality except for send was deprecated in the new version of mail for SQL 2005 and 2008 called Database Mail. (The old XP’s are still there but you are not recommended to use them) This is because Microsoft made the decision to push quality upgrades through on the most commonly requested functionality and let the others go since many folks weren’t using them. If you remember SQL Mail used to be a dog. It usually required outlook on your server and had a host of performance and stability problems.
Much of this has been fixed in the new versions and I encourage you to read about them in the links I will post at the end of this note.
An interesting questions came to me recently about the need for a wrapper to use for email in an environment where all the XP_Sendmail references could not be changed prior to upgrade but some new Database Mail functionality was in place. The parameters are different in some cases between the two methods for sending mail, and database mail has some defaults the XP_Sendmail commands cannot handle.
Check out this solution below. I believe you’ll find it will work for these situations. This wrapper is a combined creation from the folks making the request and me helping them wok through it. It allows you to replace XP_SendMail in your code without changing the parameter names or values. They will be automatically mapped to the new procedure if the version supports it or left alone if you’re leaving SQL Mail in place after your migration.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[EmailWrapper]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[EmailWrapper]
GO
/*--------------------------------------------------------------------------------------------
Description: This procedure serves as a wrapper to abstract from the parameters of sp_send_dbmail in SQL Server 2005 and 2008. It provides a standard set of
variables that can be mapped to the database mail send procedure and serves as a replacement in everyday code. It accepts the same parameters
(under new names) as the database mail procedure sp_send_dbmail, and executes sp_send_dbmail with those values as defined by the business process
logic in the code.
There is a TRY/CATCH block implemented for error handling incase of invalid data. The procedure will return the error output from sp_send_dbmail.
See Test Examples for more information.
TEST:
Exec msdb.dbo.EmailWrapper
@profile_name = SAMPLE PROFILE'
,@recipients = 'You@YourOrganization.com'
-- ,@copy_recipients
-- ,@blind_copy_recipients
,@subject = 'Test of Mail Wrapper'
,@message = 'Body of Wrapper Proc'
--,@body_format
--,@importance
--,@sensitivity
--,@file_attachments
--,@query
--,@execute_query_database
--,@attach_query_result_as_file
--,@query_attachment_filename
--,@query_result_header
--,@query_result_width
--,@query_result_separator
--,@exclude_query_output
--,@append_query_error
--,@query_no_truncate
-- TEST CODE TO RAISE ERROR
Exec msdb.dbo.EmailWrapper
@profile_name = 'Inproper' -- ****** INVALID PROFILE NAME WILL TRIGGER ERROR
,@recipients = 'You@YourOrganization.com'
-- ,@copy_recipients
-- ,@blind_copy_recipients
,@subject = 'Test of Mail Wrapper'
,@message = 'Body of Wrapper Proc'
--,@body_format
--,@importance
--,@sensitivity
--,@file_attachments
--,@query
--,@execute_query_database
--,@attach_query_result_as_file
--,@query_attachment_filename
--,@query_result_header
--,@query_result_width
--,@query_result_separator
--,@exclude_query_output
--,@append_query_error
--,@query_no_truncate
---------------------------------------------------------------------------------------------*/
CREATE PROCEDURE DBO.EmailWrapper
@recipients varchar(1000)
, @message varchar(8000) = 'SQL Server Message'
, @query varchar(8000) = NULL
, @attachments varchar(8000) = NULL
, @copy_recipients varchar(1000) = NULL
, @blind_copy_recipients varchar(1000) = NULL
, @subject varchar(1000) = 'SQL Server Message'
, @type char(3) = NULL
, @attach_results varchar(5) = 'FALSE'
, @no_output varchar(5) = 'FALSE'
, @no_header varchar(5) = 'FALSE'
, @width smallint = 256
, @separator char(1) = ' '
, @echo_error varchar(5) = 'FALSE'
, @set_user varchar(255) = NULL
, @dbuse varchar(255) = NULL
, @profile_name sysname = NULL -- Database Mail parameter; do not use with SQL Mail
, @body_format char(4) = 'TEXT' -- Database Mail parameter; do not use with SQL Mail
, @importance varchar(6) = 'Normal' -- Database Mail parameter; do not use with SQL Mail
, @sensitivity varchar(12) = 'Normal' -- Database Mail parameter; do not use with SQL Mail
, @query_attachment_filename nvarchar(255) = NULL -- Database Mail parameter do not use with SQL Mail
, @append_query_error bit = 0 -- Database Mail parameter; do not use with SQL Mail
, @query_no_truncate bit = 0 -- Database Mail parameter; do not use with SQL Mail
, @query_result_no_padding BIT = 0 -- Database Mail parameter do not use with SQL Mail
AS
SET NOCOUNT ON
--CHECK FOR VALID EMAIL ADDRESS
IF @recipients IS NULL OR @recipients NOT LIKE '%@%.%'
BEGIN
RAISERROR( 'Must pass at least one email recipient to send an email.', 16, 1 )
RETURN -999
END
BEGIN TRY
-- LET MAIL SYSTEM PROCS VALIDATION OF ATTACHEMTNS, ETC..
IF @@VERSION LIKE '%SQL Server 2005%' OR @@VERSION LIKE '%SQL Server 2008%'
BEGIN
-- MAP DATABASE MAIL PARAMETERS TO SQL MAIL PARAMETERS
DECLARE
@body nvarchar(max),
@file_attachments nvarchar(max),
@attach_query_result_as_file bit,
@exclude_query_output bit,
@query_result_header bit,
@query_result_width int,
@query_result_separator char(1),
@execute_query_database sysname,
@mailitem_id int
SELECT @body=cast(@message as nvarchar(max)),
@file_attachments=cast(@attachments as nvarchar(max)),
@attach_query_result_as_file = case @attach_results when 'TRUE' then 1 when 'FALSE' then 0 else 0 end,
@exclude_query_output = case @no_output when 'TRUE' then 1 when 'FALSE' then 0 else 0 end,
@query_result_header = case @query_result_header when 'TRUE' then 1 when 'FALSE' then 0 else 0 end,
@query_result_width = @width,
@query_result_separator = case @query_result_separator when 'TRUE' then 1 when 'FALSE' then 0 else 0 end,
@execute_query_database = cast(@dbuse as sysname)
--EXECUTE DATABASE MAIL SEND PROCEDURE
EXEC msdb.dbo.sp_send_dbmail @profile_name
, @recipients
, @copy_recipients
, @blind_copy_recipients
, @subject
, @body
, @body_format
, @importance
, @sensitivity
, @file_attachments
, @query
, @execute_query_database
, @attach_query_result_as_file
, @query_attachment_filename
, @query_result_header
, @query_result_width
, @query_result_separator
, @exclude_query_output
, @append_query_error
, @query_no_truncate
, @query_result_no_padding
, @mailitem_id OUTPUT
END
ELSE
IF @@VERSION LIKE '%SQL Server 2000%'
BEGIN
EXEC master.dbo.xp_sendmail @recipients
, @message
, @query
, @attachments
, @copy_recipients
, @blind_copy_recipients
, @subject
, @type
, @attach_results
, @no_output
, @no_header
, @width
, @separator
, @echo_error
, @set_user
, @dbuse
END
END TRY
--Retrieve Error Information
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
SET NOCOUNT OFF
GO
