October 4, 2004 at 4:35 am
i have written a trigger that fires once an update on a specific field takes place, this trigger is meant to then run a select statement and send an e-mail containing the results of this select statement.
My problem is with getting the results of the select statement into a format suitable for feeding to the sp_send_cdontsmail stored procedure. This sp accepts a string for the body of the e-mail.
Any ideas how i can send results from the select query into one string (prefereably with line breaks per row). I am at a loss so areally need some help with this one.
Thanks, ade
October 4, 2004 at 4:39 am
I wouldn't recommend sending email from within a trigger. It will bite you eventually.
It's better to have the trigger log the mails to be sent in a queue table, then you can have a separate job / task that scans the queue table and sends the mail at some appropriate interval.
/Kenneth
October 4, 2004 at 4:40 am
Thanks, for the reply, i am quite new to SQL server so could you explain a little why this will "bite" me??
October 4, 2004 at 5:00 am
Basically, you want triggers to be as lean as possible, else they are in the way, and you're in great risk of getting trouble with the transaction, may create concurrency problems, resource hogging etc...
So, sending mail, printing or any other 'external' activity inside a trigger is a baaaad idea. They aren't designed for that type of activities, therefore I recommend against it.
/Kenneth
October 4, 2004 at 6:05 am
Just to add to what Kenneth has said (which is VERY good)... TRIGGERS may LOCK the table and STOP ALL ACTIVITY on the table until they are DONE.
This means that NO ONE will be able to access the table that the TRIGGER is busy sending e-mail on..
Good Hunting!
AJ Ahrens
webmaster@kritter.net
October 4, 2004 at 7:09 am
ok, thanks for all the advice and i will take it on board. this still leaves me with the problem of taking the all rows from a select query and sending them to sp_send_cdontsmail which only accepts a singel string!!
thanks for all the help so far, ade
October 5, 2004 at 3:22 am
If its a single email then select @var = field1 +' ' +Field2 + ' ' + filed3 ... from table
exec sp_send_cdontsmail .. @message=@var
RGDS
Andy Llewellyn
October 6, 2004 at 11:24 am
If you need to send multiple rows, here's one way. This procedure sends up to 3000 characters at a time. If the query returns more than 3000 characters, it breaks the results up and sends separate messages so that the string variable does not overflow. the char(13) provides the line breaks. I'm not familiar with sp_send_cdontsmail so I don't know it's limitation on message size. I hope this message doesn't get truncated.
Good luck.
create procedure dbo.SCAN_WHSE_STATUS AS
DECLARE @curr_tablename sysname
DECLARE @SQLString nvarchar(4000)
DECLARE @TABLE_TYPE varchar (7),
@TABLE_NAME varchar (40),
@COUNT_ROWS int ,
@SERVICE_DATE_MAX datetime ,
@DATA_DATE datetime
DECLARE mail_cursor CURSOR FOR
select
isnull(w1.table_type, ''),
isnull(w1.TABLE_NAME, ''),
isnull(w1.COUNT_ROWS, ''),
isnull(w1.SERVICE_DATE_MAX,''),
isnull(w1.DATA_DATE,'')
FROM dbo.whse_data_status w1
ORDER BY w1.table_name
set @sqlstring = 'Test Notification of Warehouse Updates' + char(13)
OPEN mail_cursor
FETCH NEXT FROM mail_cursor
INTO
@TABLE_TYPE ,
@TABLE_NAME,
@COUNT_ROWS ,
@SERVICE_DATE_MAX,
@DATA_DATE
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQLString = @sqlstring +
'Table_name = ' +@table_name + char(13) +
'Table type = ' + @table_type + char(13) +
'Row count = ' + cast(@count_rows as varchar) + char(13) +
'Maximum Service Date = ' + cast(@service_date_max as varchar) + char(13) +
'Data Check Date =' + cast(@data_date as varchar) + char(13) +
'============================================' + char(13)
--print @SQLSTring
-- If the length of the string is greater than 3000, it is time
--to send it as a separate email and then reset @sqlstring to the top line
--title to start a new message.
if len(@sqlstring) > 3000
begin
exec master..sp_sendsmtpmail 'name@emailaddress', 'Test notification of Warehouse Updates', @sqlstring
set @sqlstring = 'Test Notification of Warehouse Updates' + char(13)
end
-- Get the next record
FETCH NEXT FROM mail_cursor
INTO
@TABLE_TYPE ,
@TABLE_NAME,
@COUNT_ROWS ,
@SERVICE_DATE_MAX,
@DATA_DATE
END
CLOSE mail_cursor
DEALLOCATE mail_cursor
if len(@sqlstring) > 0
begin
exec master..sp_sendsmtpmail 'email address', 'Test notification of Warehouse Updates', @sqlstring
set @sqlstring = 'Test Notification of Warehouse Updates' + char(13)
end
set @sqlstring = ''
return
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply