Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CORRECTIONS TO SP_SENDMAIL FOR XP_SENDMAIL ON SQL2005 Expand / Collapse
Author
Message
Posted Wednesday, August 20, 2008 2:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 27, 2009 11:17 AM
Points: 2, Visits: 12
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]
@recipients varchar (max) = NULL,
@message nvarchar(max) = NULL,
@query nvarchar(max) = NULL,
@attachments nvarchar(max) = NULL,
@copy_recipients varchar (max) = NULL,
@blind_copy_recipients varchar (max) = NULL,
@subject nvarchar(255) = NULL,
@type sysname = NULL,
@attach_results char(5) = NULL,
@no_output char(5) = NULL,
@no_header char(5) = 'FALSE',
@width int = 256,
@separator char(1) = '',
@echo_error char(5) = NULL,
@set_user sysname = NULL,
@dbuse sysname = 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_attachment varchar(5) = NULL,
--call parameters outside the range of old xp_sendmail call
@profile_name sysname = NULL,
@body_format VARCHAR (20) = 'TEXT',
@importance VARCHAR (6) = 'NORMAL',
@sensitivity VARCHAR (12) = 'NORMAL',
@file_attachments NVARCHAR(MAX) = NULL,
@query_attachment_filename NVARCHAR(260) = NULL,
@append_query_error BIT = 0,
@query_no_truncate BIT = 0,
@query_result_no_padding BIT = 0,
@mailitem_id INT = 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:
WHO DATE DESCRIPTION
--- ---------- ---------------------------------------------------
RKH 08/14/2008 RESET @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
--
Post #556089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse