1

I am writing a stored procedure which creates 1 temporary table populates and then emails the results through if it is above a certain threshold. I am wanting to alter this so that if the results are above the same threshold it runs SP_WhO3 and emails me the results through?

BEGIN SET NOCOUNT ON;

DECLARE @QueryValue INT,
        @QueryValue2 INT,
        @EmailList NVARCHAR(255),
        @EmailList2 NVARCHAR(255),
        @CellList NVARCHAR(255),
        @HTML NVARCHAR(MAX),
        @HTML2 NVARCHAR(MAX),
        @ServerName NVARCHAR(50),
        @EmailSubject NVARCHAR(100),
        @LastDateStamp DATETIME;

SELECT @LastDateStamp = MAX(DateStamp)
FROM [dbWarden].dbo.CPUStatsHistory;

SELECT @ServerName = CONVERT(NVARCHAR(50), SERVERPROPERTY('servername'));

SELECT @QueryValue = CAST(Value AS INT)
FROM [dbWarden].dbo.AlertSettings
WHERE VariableName = 'QueryValue'
      AND AlertName = 'CPUAlert'
      AND [Enabled] = 1;

SELECT @QueryValue2 = CAST(Value AS INT)
FROM [dbWarden].dbo.AlertSettings
WHERE VariableName = 'QueryValue2'
      AND AlertName = 'CPUAlert'
      AND [Enabled] = 1;

SELECT @EmailList = EmailList,
       @EmailList2 = EmailList2,
       @CellList = CellList
FROM [dbWarden].dbo.AlertContacts
WHERE AlertName = 'CPUAlert';

CREATE TABLE #TEMP
(
    [SQLProcessPercent] INT,
    [SystemIdleProcessPercent] INT,
    [OtherProcessPerecnt] INT,
    DateStamp DATETIME
);

INSERT INTO #TEMP
EXEC [dbWarden].dbo.usp_CPUStats;

IF EXISTS
(
    SELECT *
    FROM #TEMP
    WHERE SQLProcessPercent > @QueryValue
          AND DateStamp > COALESCE(@LastDateStamp, GETDATE() - 1)
)
BEGIN

    SET @HTML
        = N'<html><head><style type="text/css">
        table { border: 0px; border-spacing: 0px; border-collapse: collapse;}
        th {color:#FFFFFF; font-size:12px; font-family:arial; background-color:#7394B0; font-weight:bold;border: 0;}
        th.header {color:#FFFFFF; font-size:13px; font-family:arial; background-color:#41627E; font-weight:bold;border: 0;}
        td {font-size:11px; font-family:arial;border-right: 0;border-bottom: 1px solid #C1DAD7;padding: 5px 5px 5px 8px;}
        </style></head><body>
        <table width="700"> <tr><th class="header" width="700">High CPU Alert</th></tr></table> 
        <table width="700">
        <tr>  
        <th width="150">SQL Percent</th>    
        <th width="150">System Idle Percent</th>  
        <th width="150">Other Process Percent</th>  
        <th width="200">Date Stamp</th>
        </tr>';
    SELECT @HTML
        = @HTML + N'<tr>
        <td bgcolor="#E0E0E0" width="150">' + CAST(SQLProcessPercent AS NVARCHAR)
          + N'</td>
        <td bgcolor="#F0F0F0" width="150">' + CAST(SystemIdleProcessPercent AS NVARCHAR)
          + N'</td>
        <td bgcolor="#E0E0E0" width="150">' + CAST(OtherProcessPerecnt AS NVARCHAR)
          + N'</td>
        <td bgcolor="#F0F0F0" width="200">' + CAST(DateStamp AS NVARCHAR) + N'</td> 
        </tr>'
    FROM #TEMP
    WHERE SQLProcessPercent > @QueryValue
          AND DateStamp > COALESCE(@LastDateStamp, GETDATE() - 1);

    SELECT @HTML = @HTML + N'</table></body></html>';

    SELECT @EmailSubject = N'[dbWarden]High CPU Alert on ' + @ServerName + N'!';

    EXEC msdb..sp_send_dbmail @profile_name = 'SQLAdmins',
                              @recipients = @EmailList,
                              @copy_recipients = @EmailList2,
                              @subject = @EmailSubject,
                              @body = @HTML,
                              @body_format = 'HTML';

    --IF @CellList IS NOT NULL
    --BEGIN
    --  /*TEXT MESSAGE*/
    --  IF EXISTS (SELECT * FROM #TEMP WHERE SQLProcessPercent > COALESCE(@QueryValue2, @QueryValue))
    --  BEGIN
    --      SET @HTML =
    --          '<html><head></head><body><table><tr><td>CPU,</td><td>Idle,</td><td>Other,</td><td>Date</td></tr>'
    --      SELECT @HTML =  @HTML +   
    --          '<tr><td>' + CAST(SQLProcessPercent AS NVARCHAR) +',</td><td>' + CAST(SystemIdleProcessPercent AS NVARCHAR) +',</td><td>' + CAST(OtherProcessPerecnt AS NVARCHAR) +',</td><td>' + CAST(DateStamp AS NVARCHAR) + '</td></tr>'
    --      FROM #TEMP WHERE SQLProcessPercent > COALESCE(@QueryValue2, @QueryValue)

    --      SELECT @HTML =  @HTML + '</table></body></html>'

    --      SELECT @EmailSubject = '[dbWarden]HighCPUAlert-' + @ServerName

    --      EXEC msdb..sp_send_dbmail
    --      @recipients= @CellList,
    --      @subject = @EmailSubject,
    --      @body = @HTML,
    --      @body_format = 'HTML'

    --  END
    --END
    CREATE TABLE #TEMP2
    (
        SPID INT,
        [Blk By] INT,
        [Elapsed MS] INT,
        CPU INT,
        [I/O Reads] INT,
        [I/O Writes] INT,
        Executions INT,
        [Command Type] VARCHAR(255),
        [Last Wait Type] VARCHAR(100),
        [Object Name] VARCHAR(255) NULL,
        [SQL Statement] NVARCHAR(MAX),
        [Status] NVARCHAR(20),
        [Login] NVARCHAR(100),
        Host NVARCHAR(50),
        DBNAME NVARCHAR(50),
        [Start Time] NVARCHAR(250),
        protocol VARCHAR(50),
        [Transaction Isolation] VARCHAR(50),
        [Connection Writes] NVARCHAR(250),
        [Connection Reads] NVARCHAR(250),
        [Client address] NVARCHAR(50),
        [Authentication] NVARCHAR(25),
        [Date Time Snapshot] NVARCHAR(250),
        [Plan Handle] NVARCHAR(MAX)
    );

    INSERT INTO #TEMP2
    EXEC dbo.sp_who3;

    IF EXISTS
    (
        SELECT SPID,
               [Blk By],
               [Elapsed MS],
               CPU,
               [I/O Reads],
               [I/O Writes],
               Executions,
               [Command Type],
               [Last Wait Type],
               [SQL Statement],
               Status,
               Login,
               Host,
               DBNAME,
               [Start Time],
               [Client address],
               Authentication,
               [Date Time Snapshot]
        FROM #TEMP2
    )
    BEGIN

        SET @HTML2
            = N'<html><head><style type="text/css">
        table { border: 0px; border-spacing: 0px; border-collapse: collapse;}
        th {color:#FFFFFF; font-size:12px; font-family:arial; background-color:#7394B0; font-weight:bold;border: 0;}
        th.header {color:#FFFFFF; font-size:13px; font-family:arial; background-color:#41627E; font-weight:bold;border: 0;}
        td {font-size:11px; font-family:arial;border-right: 0;border-bottom: 1px solid #C1DAD7;padding: 5px 5px 5px 8px;}
        </style></head><body>
        <table width="80000"> <tr><th class="header" width="80000">CPU ALERTS</th></tr></table> 
        <table width="80000">
        <tr> 
        <th width="100">SPID</th>   
        <th width="150">Blk By</th>  
        <th width="200">Elapsed MS</th>  
        <th width="100">CPU</th>    
        <th width="125">I/O Reads</th>
        <th width="125">I/O Writes</th> 
        <th width="100">Executions</th>  
        <th width="100">Command Type</th>  
        <th width="150">Last Wait Type</th> 
        <th width="10000">SQL Statement</th>
        <th width="100">Status</th>  
        <th width="100">Login</th>  
        <th width="150">Host</th>   
        <th width="125">DBNAME</th>
        <th width="150">Start Time</th>
        <th width="100">Client Address</th> 
        <th width="100">Authentication</th>  
        <th width="250">Date Time Snapshot</th>     
        </tr>';
        SELECT @HTML2
            = @HTML2 + N'<tr>
        <td bgcolor="#E0E0E0" width="100">' + CAST([SPID] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#E0E0E0" width="150">' + CAST([Blk By] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="200">' + CAST([Elapsed MS] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#E0E0E0" width="100">' + CAST([CPU] AS NVARCHAR)
              + N'</td> 
        <td bgcolor="#F0F0F0" width="125">' + CAST([I/O Reads] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="125">' + CAST([I/O Writes] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="100">' + CAST([Executions] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="100">' + CAST([Command Type] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="150">' + CAST([Last Wait Type] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="10000">' + CAST([SQL Statement] AS NVARCHAR(MAX))
              + N'</td>
        <td bgcolor="#F0F0F0" width="100">' + CAST([Status] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="100">' + CAST([Login] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="150">' + CAST([Host] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="125">' + CAST([DBNAME] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="150">' + CAST([Start Time] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="100">' + CAST([Client address] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="100">' + CAST([Authentication] AS NVARCHAR)
              + N'</td>
        <td bgcolor="#F0F0F0" width="250">' + CAST([Date Time Snapshot] AS NVARCHAR) + N'</td>
        </tr>'
        FROM #TEMP2;

        SELECT @HTML2 = @HTML2 + N'</table></body></html>';

        SELECT @EmailSubject = N'CPU ALERTS' + N' ' + @ServerName;

        EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLAdmins',
                                     @recipients = 'censor@cngltd.co.uk',
                                     @subject = @EmailSubject,
                                     @body = @HTML2,
                                     @body_format = 'HTML';
    END;
END;

END;

When this runs it fires the emails as expected but the second email (SP_WHO3) is blank. Am I missing something?

  • (1) you should always case nvarchar() with an explicit length (see this post) and (2) are you guarding against NULLs? To see why (2) is important try SELECT 'foo' + NULL; – Aaron Bertrand Mar 12 '19 at 12:58
1

I am guessing that some value in #temp2 IS NULL, this results in eveything returning NULL

Try changing the columns from #temp2 and adding ISNULL() functions to change the NULL's to empty strings ''.

If you execute this:

SELECT NULL +'MailContent'

it returns

(No column name)
NULL

Example of changes in your query

SELECT @HTML2
    = @HTML2 + N'<tr>
<td bgcolor="#E0E0E0" width="100">' + ISNULL(CAST([SPID]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#E0E0E0" width="150">' +ISNULL(CAST([Blk By]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="200">' +ISNULL(CAST([Elapsed MS]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#E0E0E0" width="100">' +ISNULL(CAST([CPU]AS NVARCHAR), '')
      + N'</td> 
<td bgcolor="#F0F0F0" width="125">' +ISNULL(CAST([I/O Reads]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="125">' +ISNULL(CAST([I/O Writes]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="100">' +ISNULL(CAST([Executions]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="100">' +ISNULL(CAST([Command Type]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="150">' +ISNULL(CAST([Last Wait Type]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="10000">' +ISNULL(CAST([SQL Statement] AS NVARCHAR(MAX),'')
      + N'</td>
<td bgcolor="#F0F0F0" width="100">' +ISNULL(CAST([Status]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="100">' +ISNULL(CAST([Login]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="150">' +ISNULL(CAST([Host]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="125">' +ISNULL(CAST([DBNAME]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="150">' +ISNULL(CAST([Start Time]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="100">' +ISNULL(CAST([Client address]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="100">' +ISNULL(CAST([Authentication]AS NVARCHAR), '')
      + N'</td>
<td bgcolor="#F0F0F0" width="250">' +ISNULL(CAST([Date Time Snapshot]AS NVARCHAR), '') + N'</td>
</tr>'
FROM #TEMP2;
| improve this answer | |
  • 1
    Perfect, thanks Randi I'll give it a try. – Craig Ensor Mar 12 '19 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.