Wednesday, June 06, 2007

replicating the functionality of xp_smtp_sendmail in Sql 2005 x64

Here's something interesting that I've come across in the course of a project to upgrade from Sql 2000 to Sql 2005 64 bit. System was using xp_smtp_sendmail from stored procedures to send mail - and was customizing the @from input to make it appear as though the email was coming from the clients account manager. Turns out that in Sql 2005, the sp_send_dbmail works a little different. Instead of specifying a string as the from email, you input a profile name. The profile name exists on the server and contains the from address as well other information like the location of the smtp server and port to send on. Using xp_smtp_sendmail is not really an option (from what I've read) because the DLL is a 32 bit DLL. So what to do?

In this thread in Google Groups, here's some discussion regarding the problem. Below I attempt to implement the suggested solution in the post. This is a first pass, as I'd like to have some error handling in here as well, so I might update this later.



create proc custom_send_mail
@From varchar(100) ,
@To varchar(355) ,
@CC varchar(300) = NULL,
@BCC varchar(300) = NULL,
@Subject varchar(100)= ' ',
@Body varchar(8000) = ' ',
@attachments varchar(4000)=NULL,
@HTML_IND int = 0
AS

-- This used to call xp_smtp_sendmail!

declare @email_address nvarchar(128)
set @email_address = @From

--sp_help sysmail_account
declare @smtp_server varchar(100)
declare @profile_name sysname
declare @accountID int
declare @profileID int
declare @bodyFormat varchar(100)
set @bodyFormat = case when @HTML_IND = 0 then 'TEXT' else 'HTML' end

set @profile_name = NULL
set @accountID = 0
set @profileID = 0
set @smtp_server = 'your_smtp_server_here'

-- Check to see if there is already an existing profile
select top 1 @profile_name = [name]
from msdb.dbo.sysmail_account a
where a.email_address = @email_address


if @profile_name is NULL
begin
-- Generate a new profile/account name (based on a guid)
set @profile_name = left(replace(convert(varchar(128), newid()),'-',''),30)

-- create the account
exec msdb.dbo.sysmail_add_account_sp
@account_name = @profile_name
, @email_address = @email_address
, @display_name = @email_address
, @replyto_address = @email_address
, @description = 'System Generated Profile for Ad Hoc Email'
, @mailserver_name = @smtp_server
, @mailserver_type = 'SMTP'
, @port = 25
, @username = NULL
, @password = NULL
, @use_default_credentials = 0
, @enable_ssl = 0
, @account_id = @accountID out

-- do I need a profile as well? Yup, I do.
if @accountID > 0
begin
-- Create the profile
exec msdb.[dbo].[sysmail_add_profile_sp]
@profile_name = @profile_name,
@description = 'System Generated Profile for Ad Hoc Email',
@profile_id = @profileID OUTPUT

-- Add the relationship between the profile and the account
exec msdb.[dbo].[sysmail_add_profileaccount_sp]
@profile_id = @profileID,
@profile_name = @profile_name,
@account_id = @accountID,
@account_name = @profile_name,
@sequence_number = 1
end

end



-- Now send the email using the profile name (optionally created).

exec msdb..sp_send_dbmail
@profile_name = @profile_name
, @recipients = @To
, @copy_recipients = @CC
, @blind_copy_recipients = @BCC
, @subject = @Subject
, @body = @Body
, @body_format = @bodyFormat
-- [, [ @importance = ] 'importance' ]
-- [ , [ @sensitivity = ] 'sensitivity' ]
, @file_attachments = @attachments
-- [ , [ @query = ] 'query' ]
-- [ , [ @execute_query_database = ] 'execute_query_database' ]
-- [ , [ @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 = ] query_result_width ]
-- [ , [ @query_result_separator = ] 'query_result_separator' ]
-- [ , [ @exclude_query_output = ] exclude_query_output ]
-- [ , [ @append_query_error = ] append_query_error ]
-- [ , [ @query_no_truncate = ] query_no_truncate ]
-- [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

No comments: