StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Explanation:</strong></p> <p>My guess is that the code you are actually using is slightly different from the code you have posted here because when I take your code and the following data in a test database, things work out fine.</p> <pre><code>create table email_archive ( id int, emailed_to nvarchar(255) ) insert into email_archive values ( 1, 'one@helpme.com'), ( 2, 'two@helpme.com'), ( 3, 'three@helpme.com'), ( 4, 'four@helpme.com'), ( 5, 'one@helpme.com'), ( 6, 'two@helpme.com'), ( 7, 'three@helpme.com'), ( 8, 'four@helpme.com'), ( 9, 'one@helpme.com'), (10, 'two@helpme.com'), (11, 'three@helpme.com'), (12, 'four@helpme.com'), (13, 'one@helpme.com'), (14, 'two@helpme.com'), (15, 'three@helpme.com'), (16, 'four@helpme.com'), (17, 'one@helpme.com'), (18, 'one@helpme.com'), (19, 'one@helpme.com'), (20, 'three@helpme.com'), (21, 'three@helpme.com') </code></pre> <p>I am thinking you may have hit upon an issue discussed here: <a href="http://bit.ly/cMlnjt" rel="nofollow">http://bit.ly/cMlnjt</a></p> <p>Since I can't be sure I offer you two alternative solutions that will definitely get the job done, even though <em>as others have mentioned</em> this aggregate concatenation <em>should</em> work without an issue.</p> <p><strong>Alternatives:</strong></p> <p>To get what you are looking for, I prefer one of the following two options</p> <p>1) Just make sp_send_dbmail do the work for you.</p> <p>2) Go with a cursor solution</p> <p><strong>Option 1:</strong></p> <pre><code>EXEC msdb..sp_send_dbmail @profile_name = 'MyMailProfile', @recipients = 'my_email@domain.com', @subject = 'Runaway Email Monitor', @body = 'Runaway emails found', @query = 'SELECT COUNT(*), emailed_to FROM mydb.dbo.email_archive GROUP BY emailed_to HAVING COUNT(*) > 5 ORDER BY COUNT(*) DESC' </code></pre> <p>Note: The having clause makes this only display rows where the count is greater than 5.</p> <p><strong>Option 2:</strong></p> <pre><code>USE test IF EXISTS ( SELECT name FROM test.sys.sysobjects WHERE type = 'P' AND name = 'usp_MonitorEmails' ) BEGIN DROP PROCEDURE dbo.usp_MonitorEmails END GO CREATE PROCEDURE usp_MonitorEmails @Subject nvarchar(255) = '', @Importance varchar(6) = 'NORMAL', @Sensitivity varchar(12) = 'NORMAL', @Recipients varchar(MAX) = NULL, @MinimumCount int = 0 AS BEGIN SET NOCOUNT ON IF UPPER(@Importance) NOT IN ('LOW', 'NORMAL', 'HIGH') SET @Importance = 'NORMAL' IF UPPER(@Sensitivity) NOT IN ('NORMAL', 'PERSONAL', 'PRIVATE', 'CONFIDENTIAL') SET @Sensitivity = 'NORMAL' DECLARE @run bit, @message nvarchar(MAX) SELECT @run = 0, @subject = 'Run Away Email Monitor', @message = 'Run away emails found' + CHAR(13)+CHAR(10) + 'Count Email Address' + CHAR(13)+CHAR(10) + '----------- ------------------------------------------------------------------------------' + CHAR(13)+CHAR(10) DECLARE @count int, @email nvarchar(255) DECLARE BodyCursor CURSOR STATIC FOR SELECT COUNT(*), emailed_to FROM email_archive GROUP BY emailed_to HAVING COUNT(*) > @MinimumCount ORDER BY COUNT(*) DESC OPEN BodyCursor FETCH NEXT FROM BodyCursor INTO @count, @email WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = @message + REPLICATE(N' ', 11-LEN(CAST(@count AS nvarchar(22)))) + CAST(@count AS nvarchar(22)) + ' ' + @email + CHAR(13)+CHAR(10), @run = 1 FETCH NEXT FROM BodyCursor INTO @count, @email END CLOSE BodyCursor DEALLOCATE BodyCursor IF @run = 1 AND LEN(@Recipients) > 0 BEGIN EXEC msdb..sp_send_dbmail @profile_name = 'MyMailProfile', @recipients = @Recipients, @subject = @Subject, @body = @Message, @body_format = 'TEXT', @importance = @Importance, @sensitivity = @Sensitivity END END GO </code></pre> <p><strong>Note:</strong> I prefer this method because of the flexibility I have in the way the messages are formatted. This will also <em>only</em> send the email if there are rows returned where the minimum count is reached.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload