How can I get a stored procedure to create 2 temporary tables and send 2 emails from the separate tables?

Question

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 = '[email protected]',
                                     @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, Commented Mar 12, 2019 at 12:58

Randi Vertongen · Accepted Answer · 2019-03-12 10:12:19Z

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;

Perfect, thanks Randi I'll give it a try.
– Craig Ensor
Commented Mar 12, 2019 at 10:38 — Craig Ensor, Commented Mar 12, 2019 at 10:38

Stack Exchange Network

How can I get a stored procedure to create 2 temporary tables and send 2 emails from the separate tables?

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2016
stored-procedures
or ask your own question.

Hot Network Questions

How can I get a stored procedure to create 2 temporary tables and send 2 emails from the separate tables?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2016stored-procedures or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2016
stored-procedures
or ask your own question.