Sql-server – How to get a stored procedure to create 2 temporary tables and send 2 emails from the separate tables

sql serversql-server-2016stored-procedures

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?

Best Answer

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;

Related Solutions

Sql-server – How to create a stored procedure using xp_fixeddrives

I've recreated your scenario on my test server that has 2 linked servers that were created manually.

CREATE TABLE SERVERNAME
(
    [Name] nvarchar(255)
);

INSERT INTO SERVERNAME ([Name]) SELECT name from SYS.SERVERS;    
GO

CREATE PROCEDURE freespace
AS
BEGIN
    CREATE TABLE #freespace
    (
        drive VARCHAR(50)
        , freedrivespace VARCHAR(50)
    );
    DECLARE @servername VARCHAR(50);
    DECLARE @sql VARCHAR(100);
    DECLARE curspace CURSOR LOCAL FORWARD_ONLY FOR
    SELECT [Name] FROM SERVERNAME;
    OPEN curspace;
    FETCH NEXT FROM curspace INTO @servername;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = '[' + @servername + ']...xp_fixeddrives';
        INSERT INTO #freespace
        EXEC @sql;
        FETCH NEXT FROM curspace INTO @servername;
    END
    CLOSE curspace;
    DEALLOCATE curspace;
    SELECT * FROM #freespace;
END
GO

EXEC FREESPACE;

The results for my server are:

drive   freedrivespace
C       75305
I       277458
K       861878
C       24378
D       90630
F       7575392
G       972578
H       768495
I       50952
J       7150657
C       13799
D       25162
F       6745607
G       982818
H       580167
I       50881
J       7224593

Granted the results are not exactly usable since the name of the server is not shown, however it does work. This tells me the problem is not in your dynamic SQL; the problem lies with your linked server configuration.

Are you able to manually run a simple query such as:

SELECT * FROM [server1].master.sys.tables;

This should either return results, or most likely the same error you are seeing above.

MySQL – Stored Procedure Performance Using Temporary Tables

To answer a question that arises from the comments, temporary tables in MySQL are owned by and only available to the client connection (session, thread) that created them, so there's not an isolation issue, but there is the potential issue of your connection having left something there with that name before, either with stale data or a similarly-named table with a different structure, either of which would be less than ideal.

The CREATE TEMPORARY TABLE IF NOT EXISTS statement creates the temporary table if there isn't one of the same name, but is a non-blocking no-op if the table is already there, which, as noted, is still bad. The better alternative is probably to drop the temporary table at the top of the proc, if it's there, before creating it, so you always begin with a clean environment. This is similarly a no-op if the temporary table isn't there:

   DROP TEMPORARY TABLE IF EXISTS BATCH_ITEMS_UOW;

Important: if you do not have an index on the STATUS column of the BATCH_ITEMS table, this will be one of the two major reasons this procedure is slower than it needs to be. That's going to be essential for performance, either with this code or the existing code.

It would probably also be best to index the temporary table, to make life as easy as possible for the optimizer. Note that the column declared must be the same as the name or alias of the column in the SELECT, otherwise MySQL assumes the SELECT is supposed to generate additional columns for the temporary table using the column names in the SELECT:

   CREATE TEMPORARY TABLE BATCH_ITEMS_UOW (
     ID INT NOT NULL PRIMARY KEY
   ) ENGINE=MEMORY AS (
    SELECT ID FROM BATCH_ITEMS 
     WHERE STATUS = 'UNPROCESSED' 
       AND BATCH_JOB_ID = batchId 
     LIMIT size
   );

Then instead of an update that use a subquery, this should be a join, since those are generally optimized better in any version of MySQL. This is the other likely reason this procedure may be slower than you expect.

UPDATE BATCH_ITEMS BI
  JOIN BATCH_ITEMS_UOW UOW ON UOW.ID = BI.ID
   SET BI.STATUS = 'PROCESSING';

Then return the result-set...

SELECT * FROM BATCH_ITEMS_UOW;

Optionally, you can drop the temporary table... it will be dropped automatically when your session disconnects, or the next time you run this procedure, since we're now explicitly dropping it at the start if you run this again in the same session.

DROP TEMPORARY TABLE BATCH_ITEMS_UOW;

Best Answer

Related Solutions

Sql-server – How to create a stored procedure using xp_fixeddrives

MySQL – Stored Procedure Performance Using Temporary Tables

Related Question