SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CORRECTIONS TO SP_SENDMAIL FOR XP_SENDMAIL ON SQL2005


CORRECTIONS TO SP_SENDMAIL FOR XP_SENDMAIL ON SQL2005

Author
Message
Robo-704698
Robo-704698
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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
--
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search