Trouble turning a Simple MsSQL select into a monitor

Answer 1

You will only get one value because you have no loop through the recordset containing the emails. You will need to use a CURSOR to traverse the set and build up your @errmsg variable with the contents of each record.

Nimble SQL jockeys will probably point out you could avoid the CURSOR with the correct kind of join in the query but I always find that harder than this method and if it doesn't have to be high performance it does the job.

Here's some example code;

DECLARE @myGlobalVar nvarchar(255);
DECLARE @myVarCol1 nvarchar(10); 
DECLARE @myVarCol2 nvarchar(10);

DECLARE myCursor CURSOR FOR 
SELECT col1, col2
FROM table1
WHERE wanted = 1
ORDER BY col1;

OPEN myCursor;

FETCH NEXT FROM myCursor 
INTO @myVarCol1, @myVarCol2;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @myGlobalVar = @myGlobalVar + ' ' + @myVarCol1 + ' ' + @myVarCol2;

  FETCH NEXT FROM myCursor 
  INTO @myVarCol1, @myVarCol2;
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

Always remember to FETCH NEXT inside the loop unless you want to have to kill the process! You should always CLOSE and DEALLOCATE the cursor too or you'll run out of memory eventually.

Answer 2

Explanation:

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.

create table email_archive
(
    id int,
    emailed_to nvarchar(255)
)

insert into email_archive values
    ( 1, '[email protected]'), ( 2, '[email protected]'), ( 3, '[email protected]'),
    ( 4, '[email protected]'), ( 5, '[email protected]'), ( 6, '[email protected]'),
    ( 7, '[email protected]'), ( 8, '[email protected]'), ( 9, '[email protected]'),
    (10, '[email protected]'), (11, '[email protected]'), (12, '[email protected]'),
    (13, '[email protected]'), (14, '[email protected]'), (15, '[email protected]'),
    (16, '[email protected]'), (17, '[email protected]'), (18, '[email protected]'),
    (19, '[email protected]'), (20, '[email protected]'), (21, '[email protected]')

I am thinking you may have hit upon an issue discussed here: http://bit.ly/cMlnjt

Since I can't be sure I offer you two alternative solutions that will definitely get the job done, even though as others have mentioned this aggregate concatenation should work without an issue.

Alternatives:

To get what you are looking for, I prefer one of the following two options

1) Just make sp_send_dbmail do the work for you.

2) Go with a cursor solution

Option 1:

EXEC msdb..sp_send_dbmail    @profile_name = 'MyMailProfile', 
                        @recipients = '[email protected]',
                        @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'

Note: The having clause makes this only display rows where the count is greater than 5.

Option 2:

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

Note: I prefer this method because of the flexibility I have in the way the messages are formatted. This will also only send the email if there are rows returned where the minimum count is reached.

Answer 3

I've had similar issues when trying to use this approach to concatenation in the past. Often some messing around with the SELECT resolves it. The FOR XML approach to concatenation is more robust.

I tried to reproduce the issue here though and couldn't (it worked correctly for me and output the 2 different email addresses in the concatenation).

Does this work for you? If so maybe compare the execution plans and see what's different.

set nocount on

create table #email_archive
(
emailed_to CHAR(45)
)

insert into #email_archive
select '[email protected];' union all
select '[email protected];' union all
select '[email protected];' union all
select '[email protected];' union all
select '[email protected];' union all
select '[email protected];' union all
select '[email protected];' union all
select '[email protected];' union all
select '[email protected];'


declare @errmsg     varchar(max) = '',
        @subject    nvarchar(255) = 'Run Away Email Monitor';

select @errmsg = REPLICATE(char(10),1)+
        '# of Emails'+
         REPLICATE(char(9),1)+
         'Email Address'+
         REPLICATE(CHAR(10),1); 

select @errmsg = @errmsg +REPLICATE(char(9),1)+
    CAST(COUNT(*) as CHAR(10))+
    REPLICATE(char(9),1)+ 
    CAST(MIN(emailed_to) as CHAR(45))
from 
    #email_archive
group by 
    emailed_to
order by 
    COUNT(*) desc;

print @errmsg;
drop table #email_archive;

Answer 4

The answers above were all great answers..but the simplest of all came from Mike Talley overnight.

select @errmsg = @errmsg +REPLICATE(char(9),1)+
    CAST(COUNT(*) as CHAR(10))+
    REPLICATE(CHAR(9),1)+
    cast(substring(emailed_to, 1, 45) as char(45))+
    REPLICATE(CHAR(10),1)

Once I dropped the MIN and added in the substring the monitor worked like a charm..