CORRECTIONS TO SP_SENDMAIL FOR XP_SENDMAIL ON SQL2005

  • After moving the out of QA and into production –using sp_sendmail to replace calls from old code calling xp_sendmail, I noticed that the default behavior of sp_send_dbmail in SQL2005 was not the same as the default behavior as found in SQL2000. To make emails come out the same as if the email was sent under SQL2000, after migrating to SQL2005, some defaults including "no count" and SQL2000 -> SQL2005 mapping logic was change in the proc.

    The first item was removing row counts from attached queries that did not show up in SQL2000. This was handled by adding "set nocount on;" to the @query parameter. Next I found a double negative (which is positive) was happening in the conversion from @no_header 'false' to the bit value under SQL2005 using @query_result_header. This is now corrected in the code. Also, to get the default behavior of header to work as if the call was done on a SQL2000 server, the value of 'false' was given to @no_header.

    Posted below is the corrected version:

    /****** Object: StoredProcedure [dbo].[sp_sendmail] Script Date: 08/20/2008 12:45:42 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROC [dbo].[sp_sendmail]

    @recipientsvarchar (max)=NULL,

    @messagenvarchar(max)=NULL,

    @querynvarchar(max)=NULL,

    @attachmentsnvarchar(max)=NULL,

    @copy_recipientsvarchar (max)=NULL,

    @blind_copy_recipientsvarchar (max)=NULL,

    @subjectnvarchar(255)=NULL,

    @typesysname=NULL,

    @attach_resultschar(5)=NULL,

    @no_outputchar(5)=NULL,

    @no_headerchar(5)='FALSE',

    @widthint=256,

    @separatorchar(1)='',

    @echo_errorchar(5)=NULL,

    @set_usersysname=NULL,

    @dbusesysname=NULL,

    --this was a late bug fix to the old mapi dll on SQL2000

    --it may or may not directly map to @body_format below

    --@ansi_attachment mapping to sp_send_dbmail is not listed

    @ansi_attachmentvarchar(5)=NULL,

    --call parameters outside the range of old xp_sendmail call

    @profile_namesysname=NULL,

    @body_formatVARCHAR (20)='TEXT',

    @importanceVARCHAR (6)='NORMAL',

    @sensitivityVARCHAR (12)='NORMAL',

    @file_attachmentsNVARCHAR(MAX)=NULL,

    @query_attachment_filenameNVARCHAR(260)=NULL,

    @append_query_errorBIT=0,

    @query_no_truncateBIT=0,

    @query_result_no_paddingBIT=0,

    @mailitem_idINT=NULL OUTPUT

    AS

    /**************************************************************************

    -- sp_sendmail.sql --

    DESCRIPTION: Subsitute call of SQL2000/5 xp_sendmail

    with a call to sp_send_dbmail

    DEPENDENCIES:

    All referring code calling xp_sendmail should be changed to

    call master.dbo.sp_sendmail or another database other than

    master, where this proc should be compiled (example msdb).

    This proc calls msdb.dbo.sp_send_dbmail using the default

    profile and fails if the mail profile is not setup to public

    or default since the original xp_sendmail did not map 'profile'

    but instead used the MAPI configuration (not supported on 2005).

    PARAMETERS:

    Uses all normal xp_sendmail params mapped to sp_send_dbmail

    RETURNS:

    See sp_send_dbmail

    USAGE:

    See sp_send_dbmail

    AUTHOR:RK Hendershot

    DATE: 06/26/2008

    MODIFICATION HISTORY:

    WHODATEDESCRIPTION

    ----------------------------------------------------------------

    RKH08/14/2008RESET @NO_HEADER FLAG TO FALSE WHICH CHANGED IN PROD

    ***************************************************************************/

    BEGIN

    SET NOCOUNT ON

    -- And make sure ARITHABORT is on.

    SET ARITHABORT ON

    -- Local mappings of distant vars.

    DECLARE @attach_query_result_as_file bit,

    @query_result_header bit,

    @exclude_query_output bit,

    @rc int

    -- Default behavior under SQL 2000 was NOCOUNT

    -- Since SQL2005 calls a proc, NOCOUNT is loss

    IF (@query) IS NOT NULL set @query = 'set nocount on;'+@query;

    -- Cannot be NULL, but TRUE as 1 or FALSE as 0

    IF (@attach_results = 'TRUE') set @attach_query_result_as_file = 1;

    ELSE set @attach_query_result_as_file = 0;

    -- Cannot be NULL, but TRUE as 0 or FALSE as 1

    -- NB: no_head as FALSE is header (double neg)

    IF (@no_header = 'TRUE') set @query_result_header = 0;

    ELSE set @query_result_header = 1;

    -- Cannot be NULL, but TRUE as 0 or FALSE as 1

    -- NB: no_head as FALSE is header (double neg)

    IF (@echo_error = 'TRUE') set @exclude_query_output = 0;

    ELSE set @exclude_query_output = 1;

    -- Bug in SQLCMD requires width between 9 and 65536 - 1

    IF (@width) < 9 set @width = 9;

    ELSE IF (@width) > 65535 set @width = 65535;

    -- Call to 2005 version, all params type-cast

    -- NB @attachments was mapped to @query_attachment_filename

    -- which is not how BOL dipicted this as @file_attachments

    -- Mapping to @query_attachement_filename gave xp_sendmail

    -- behavour as far as having access rights to the files.

    EXEC @rc = msdb.dbo.sp_send_dbmail

    @profile_name=@profile_name,

    @recipients=@recipients, --override for testing

    @copy_recipients=@copy_recipients,

    @blind_copy_recipients=@blind_copy_recipients,

    @subject=@subject,

    @body=@message,

    @body_format=@body_format,

    @importance=@importance,

    @sensitivity=@sensitivity,

    @file_attachments=@attachments,

    @query=@query,

    @execute_query_database=@dbuse,

    @attach_query_result_as_file=@attach_query_result_as_file,

    @query_attachment_filename=@query_attachment_filename,

    @query_result_header=@query_result_header,

    @query_result_width=@width,

    @query_result_separator=@separator,

    @exclude_query_output=@exclude_query_output,

    @append_query_error=@append_query_error,

    @query_no_truncate=@query_no_truncate,

    @query_result_no_padding=@query_result_no_padding,

    @mailitem_id=@mailitem_id

    ;

    RETURN (@RC)

    END

    --

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply