Wednesday, June 13, 2007

Conditional Triggers

Here's an interesting problem that I've come accross:

Suppose I have a trigger that fires on a table and updates a collection of other tables. Suppose I roll out a new piece of business code that updates a large portion of the table, but I DON'T want this update to cause the trigger to fire. How do I do this? In short, I want to be able to enable - disable a trigger on a session basis. I can't use ALTER TABLE DISABLE TRIGGER because that will turn off the trigger for all concurrent sessions.

Here's an interesting solution that someone suggested in usenet that works well for me.

In the stored procedure that does the mass update, I have the following line of code:

CREATE Table #DontRunTrig (i int)

Then, I alter the trigger to have this as the as the FIRST line:

IF OBJECT_ID('tempdb..#DontRunTrig') IS NOT NULL RETURN

This allows the trigger to do it's thing on a per spid basis.

Sort of nasty looking, but it works.

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 ]