May 30, 2012 at 5:25 pm
Hi all
I am trying to implement a simple warning system using database email.
I am monitoring an application table which I can't modify. (adding a trigger affects the application). So instead I have created a tracking table which I compare and insert new records to and update records that change.
I have created a trigger on my tracking table which fires when rows are inserted or updated, and it works great for single record transactions.
However my monitoring script/process may attempt to insert or update many rows into my tracking table. I know that I can rewrite the script so that it inserts or updates each record one at a time using a foreach loop or cursor, but i was wondering if there was a better way?
(And ideally I would like to remove the trigger and place the logic with a single script that runs at a regular interval.)
I frequently hear many people say you should use single statement rather than foreach, but I can't see how I could do that. My understanding is that a stored proc must be called or executed for each iteration/record. is this correct?
So my question is: If I have a set of records can I send an email for each record using dbmail stored proc with a single statement without a foreach/cursor?.
Any help or suggestions would be greatly appreciated.
Thanks
Pete
May 30, 2012 at 5:25 pm
I know it's always a good idea to provide examples. So here is my current test script.
edit: but looking at it I realise it's proabably a bit useless :ermm:
-- 1. select query to identify records
-- 2. for each record in my select statement do the following..
DECLARE @id INT
DECLARE @woid INT
DECLARE @priority VARCHAR (20)
DECLARE @descr VARCHAR (20)
DECLARE @sendmailto VARCHAR (256)
DECLARE @subj VARCHAR (256)
DECLARE @stamp datetime
Begin
SET @stamp = GETDATE()
SELECT @woid = 143
SELECT @descr = 'test message'
SELECT @priority = '1=Critical'
SET @sendmailto = (select [sendmailto]
from [dbo].[email_list]
where priority = @priority)
SET @subj = 'Test Subject priority: ' + @priority
EXEC msdb.dbo.sp_send_dbmail @profile_name='db mail',
@recipients=@sendmailto,
@subject=@subj,
@body='Test message body'
End
Unless there is a smarter way, I would select a set of records into a cursor and call msdb.dbo.sp_send_dbmail for each record.
If there is a more efficient method of doing this I would really appreciate hearing about it.
Thanks
Peter
May 31, 2012 at 8:04 am
There is no guarantee this would be faster, but try a WHILE loop (using your records as a decrementing counter) and see if that has any better performance than a CURSOR.
May 31, 2012 at 8:34 am
there's no way, as far as i know, to use dbmail in a set based mode;
sending individual emails is one of the places I think a cursor is no problem. unless you want multiple recipients in the TO/CC/BCC fields, there's no way around it;
When i do a mass mail, of around 1200 individiual emails in a cursor, the cursor itself takes about a second to run, and it takes maybe 10 minutes tops for every email from the cursor to actually get processed as SENT by the broker behind the scenes, as it connects to the SMTP server and does it's thing.
How many emails are in your batches?
Lowell
May 31, 2012 at 8:51 am
you could build it into a xml auto format pass it into an exec statement if you wanted to do it set based, but it will eventually only come out as multiple sp_send_dbmail requests. There was a post (cant remember who from) in the last week which did this
This is an example on what I mean, not actually the real thing as this gets DB file usage
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
)
--SELECT @sql
EXECUTE sp_executesql @sql
June 1, 2012 at 1:42 pm
Loops internally but no cursor:
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @priority VARCHAR(20)= '1=Critical';
SELECT @sql = @sql + 'EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''db mail'',
@recipients = ''' + REPLACE([sendmailto],'''','''''') + ''',
@subject = ''Test Subject priority: ' + @priority + ''',
@body = ''Test message body'';
'
FROM [dbo].[email_list]
WHERE priority = @priority;
PRINT @sql;
--EXEC(@sql);
Edit: add REPLACE to guard against embedded single-quotes in sendmailto, just in case
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 1, 2012 at 1:51 pm
June 1, 2012 at 1:54 pm
Jayanth_Kurup (6/1/2012)
Have u considered using service borker...
Why would one want to re-invent the wheel? Database Mail is built atop Service Broker.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 1, 2012 at 3:10 pm
Brandie Tarvin (5/31/2012)
There is no guarantee this would be faster, but try a WHILE loop (using your records as a decrementing counter) and see if that has any better performance than a CURSOR.
Actually, probably not. The only difference between using a Temp Table/ While loop combo and a nice read only forward only (fast forward) static cursor (also known as a "FireHose Cursor", are a couple of words of syntax. There's really no difference in performance, blocking, resource usage, etc. In fact, the cursor is easier to use than the While Loop for some people.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2012 at 3:12 pm
thepotplants (5/30/2012)
Hi allI am trying to implement a simple warning system using database email.
I am monitoring an application table which I can't modify. (adding a trigger affects the application). So instead I have created a tracking table which I compare and insert new records to and update records that change.
I have created a trigger on my tracking table which fires when rows are inserted or updated, and it works great for single record transactions.
However my monitoring script/process may attempt to insert or update many rows into my tracking table. I know that I can rewrite the script so that it inserts or updates each record one at a time using a foreach loop or cursor, but i was wondering if there was a better way?
(And ideally I would like to remove the trigger and place the logic with a single script that runs at a regular interval.)
I frequently hear many people say you should use single statement rather than foreach, but I can't see how I could do that. My understanding is that a stored proc must be called or executed for each iteration/record. is this correct?
So my question is: If I have a set of records can I send an email for each record using dbmail stored proc with a single statement without a foreach/cursor?.
Any help or suggestions would be greatly appreciated.
Thanks
Pete
Do you want to send all of the rows to the same addresses or do you want to send each row to a different address based on content?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply