May 10, 2012 at 2:11 pm
This is a stored proc that is mapped to xp_smtp_sendmail(sql2000) on another server.
ALTER PROCEDURE [dbo].[uspSendMail]
@FROM VARCHAR(256) = NULL
,@FROM_NAME VARCHAR(256) = NULL
,@TO VARCHAR(256) = NULL
,@BCC VARCHAR(100) = NULL
,@subject VARCHAR(256) = NULL
,@type VARCHAR(50) = NULL
,@message VARCHAR(5000) = NULL
,@attachment VARCHAR(256) = NULL
,@server VARCHAR(50) = NULL
,@cc VARCHAR(100) = NULL
,@priority varchar(50) = null
AS
BEGIN
IF @server is null SET @server = 'relay_server'
exec ServerA.master.dbo.xp_smtp_sendmail
@FROM = @FROM
,@FROM_NAME = @FROM_NAME
,@TO = @TO
,@BCC = @BCC
,@subject = @subject
,@type = @type
,@message = @message
,@attachment = @attachment
,@server = @server
,@cc = @cc
,@priority = @priority
END
I needed to start using sql 2008 and 2005 local notifications as opposed to this.
I was going to change this proc to use – sp_send_dbmail in sql 2008 and sql 2005. This is because other procs that call this proc use these parameters. I thought it would be easier to change this proc and map parameters of sp_send_dbmail to it.
One more catch was @from and @from_name have been discontinued in DBMail SQL 2008 and 2005. So has @type.
This is what I have been trying. I tested a couple of emails. But, please let me know if this looks right to do this or if I am getting anything wrong.
ALTER proc [dbo].[uspsendmail]
@From varchar(256) = null,
@To varchar(256) ,
@cc varchar(100) = NULL,
@BCC varchar(100) = NULL,
@Subject varchar(256)= ' ',
@message varchar(5000) = ' ',
@attachments varchar(256)=NULL,
@type varchar(50) = null,
@From_name varchar(256) = null
AS
declare @smtp_server varchar(100)
declare @profile_name sysname
declare @bodyFormat varchar(100)
set @bodyFormat = case when @type = 'text/plain' then 'TEXT' else 'HTML' end
set @profile_name = 'agent_mail'
set @smtp_server = 'relay_server'
set @from = null
set @from_name=null
exec msdb..sp_send_dbmail
@profile_name = @profile_name
, @recipients = @To
, @copy_recipients = @cc
, @blind_copy_recipients = @BCC
, @subject = @Subject
, @body = @Message
, @body_format = @bodyFormat
, @file_attachments = @attachments
, @server = @smtp_server
May 10, 2012 at 2:48 pm
manny_emm (5/10/2012)
This is a stored proc that is mapped to xp_smtp_sendmail(sql2000) on another server.
ALTER PROCEDURE [dbo].[uspSendMail]@FROM VARCHAR(256) = NULL,
@FROM_NAME VARCHAR(256) = NULL,
@TO VARCHAR(256) = NULL,
@BCC VARCHAR(100) = NULL,
@subject VARCHAR(256) = NULL,
@type VARCHAR(50) = NULL,
@message VARCHAR(5000) = NULL,
@attachment VARCHAR(256) = NULL,
@server VARCHAR(50) = NULL,
@cc VARCHAR(100) = NULL,
@priority VARCHAR(50) = NULL
AS
BEGIN
IF @server IS NULL
SET @server = 'relay_server'
EXEC ServerA.master.dbo.xp_smtp_sendmail
@FROM = @FROM,
@FROM_NAME = @FROM_NAME,
@TO = @TO,
@BCC = @BCC,
@subject = @subject,
@type = @type,
@message = @message,
@attachment = @attachment,
@server = @server,
@cc = @cc,
@priority = @priority
END
I needed to start using sql 2008 and 2005 local notifications as opposed to this.
I was going to change this proc to use – sp_send_dbmail in sql 2008 and sql 2005. This is because other procs that call this proc use these parameters. I thought it would be easier to change this proc and map parameters of sp_send_dbmail to it.
One more catch was @from and @from_name have been discontinued in DBMail SQL 2008 and 2005. So has @type.
This is what I have been trying. I tested a couple of emails. But, please let me know if this looks right to do this or if I am getting anything wrong.
ALTER PROC [dbo].[uspsendmail]@From VARCHAR(256) = NULL,
@To VARCHAR(256),
@CC VARCHAR(100) = NULL,
@BCC VARCHAR(100) = NULL,
@Subject VARCHAR(256) = ' ',
@message VARCHAR(5000) = ' ',
@attachments VARCHAR(256) = NULL,
@type VARCHAR(50) = NULL,
@From_name VARCHAR(256) = NULL
AS
DECLARE @smtp_server VARCHAR(100)
DECLARE @profile_name SYSNAME
DECLARE @bodyFormat VARCHAR(100)
SET @bodyFormat = CASE WHEN @type = 'text/plain' THEN 'TEXT'
ELSE 'HTML'
END
SET @profile_name = 'agent_mail'
SET @smtp_server = 'relay_server'
SET @from = NULL
SET @from_name = NULL
EXEC msdb..sp_send_dbmail
@profile_name = @profile_name,
@recipients = @To,
@copy_recipients = @CC,
@blind_copy_recipients = @BCC,
@subject = @Subject,
@body = @Message,
@body_format = @bodyFormat,
@file_attachments = @attachments,
@server = @smtp_server
In SQL 2008 sp_send_dbmail supports parameter @from_address, but SQL 2005 does not, so that may be a limiting factor for you if the solution has to work on both platforms.
sp_send_dbmail (Transact-SQL) - SQL Server 2008 R2
In the Database Mail (not DBMail, that is something else entirely) send-mail proc the parameter @from_address can be mapped from the xp_smtp_sendmail parameters this way:
DECLARE @from_address VARCHAR(MAX);
SET @from_address = @FROM_NAME + ' <' + @FROM + '>';
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 10, 2012 at 3:15 pm
Thank you, for catching that!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply