Send CLR stored procedure results in email

Question

I need to send the results of a CLR stored procedure (I am not able to alter the sproc / clr assembly) as HTML with email. Is there a posibility to capture and format the resultset of the sproc (to email it) without using temp tables or other kind of persisting?

Answer 1

I use Ola Hallengren's SQL Server Maintenance Solution for my database backups and index optimisation here at work. I wrote a couple different stored procedures to produce emails every night when the jobs finish so I can see at a glance how long each DB backup took, how many indexes and statistics were rebuilt and on which tables, etc.

The stored procedure I wrote for the indexes and statistics is below.

You'll have to modify it to suit your own needs and source data, but as a template to send HTML email, it should work for anything.

As you've got a CLR stored procedure though, you'll have to create a temp table with the same schema as the SP returns, then do an INSERT EXEC otherwise you won't be able to use my code.

CREATE PROCEDURE dbo.spCommandLogIndexRebuildTimePerDatabase

    @Operator   sysname

AS
BEGIN
    SET NOCOUNT ON;

    /* Debug Block
    DECLARE @Operator   sysname = 'Your Operator Name';
    --*/;

    DECLARE @MaxID          int
    ,       @xml            nvarchar(MAX)
    ,       @body           nvarchar(MAX)
    ,       @subj           nvarchar(255)   = N'Index Optimise Results: ' + CAST(CAST(SYSDATETIME() AS date) AS nvarchar) + N' (' + @@SERVERNAME + N')'
    ,       @span_start     nchar(31)       = N'<span style="font-weight:bold">'
    ,       @span_end       nchar(7)        = N'</span>'
    ,       @email          varchar(255);

    -- drop temp table
    BEGIN
        IF  OBJECT_ID('tempdb..#Temp') IS NOT NULL
            DROP TABLE #Temp;
    END

    -- create temp table
    BEGIN
        CREATE TABLE #Temp
        (
            ID                  int     NOT NULL    IDENTITY    PRIMARY KEY
        ,   [Database]          sysname
        ,   [Indexes]           int
        ,   [Statistics]        int
        ,   [TotalDuration]     decimal(19, 3)
        ,   [Time]              time
        );
    END;

    -- fill temp table
    BEGIN
        -- get the starting ID of the latest group of backups
        WITH        CTEBaseData
        AS
        (
                    SELECT      l.ID
                    ,           l.CommandType
                    ,           l.DatabaseName
                    ,           l.StartTime
                    ,           l.EndTime
                    ,           DATEDIFF(MILLISECOND, l.StartTime, l.EndTime) AS DurationMS
                    ,           ROW_NUMBER() OVER (ORDER BY l.StartTime) AS RowNum

                    FROM        dbo.CommandLog l

                    WHERE       l.CommandType IN (N'ALTER_INDEX', N'UPDATE_STATISTICS')
        )

        SELECT      @MaxID = MAX(a.ID)

        FROM        CTEBaseData a

                    LEFT JOIN CTEBaseData b
                        ON a.RowNum = b.RowNum + 1

        WHERE       DATEDIFF(SECOND, ISNULL(b.EndTime, '2013-01-01'), a.StartTime) > 3600;

        -- fill the temp table
        WITH        CTEObjectTimes
        AS
        (
                    SELECT      l.DatabaseName AS [Database]
                    ,           CASE l.CommandType WHEN N'ALTER_INDEX' THEN 1 ELSE 0 END AS [Indexes]
                    ,           CASE l.CommandType WHEN N'UPDATE_STATISTICS' THEN 1 ELSE 0 END AS [Statistics]
                    ,           DATEDIFF(MILLISECOND, l.StartTime, l.EndTime) AS [Milliseconds]

                    FROM        dbo.CommandLog l

                    WHERE       l.CommandType IN (N'ALTER_INDEX', N'UPDATE_STATISTICS')
                                AND l.ID >= @MaxID
        )
        ,           CTEIndividualTotals
        AS
        (
                    SELECT      c.[Database]
                    ,           SUM(c.[Indexes]) AS [Indexes]
                    ,           SUM(c.[Statistics]) AS [Statistics]
                    ,           SUM(c.[Milliseconds]) AS [Milliseconds]

                    FROM        CTEObjectTimes c

                    GROUP BY    c.[Database]
        )
        ,           CTEResult
        AS
        (
                    SELECT      c.[Database]
                    ,           c.[Indexes]
                    ,           c.[Statistics]
                    ,           c.[Milliseconds]
                    ,           0 AS SortOrder

                    FROM        CTEIndividualTotals c

                    UNION ALL

                    SELECT      N'Total'
                    ,           SUM(c.[Indexes])
                    ,           SUM(c.[Statistics])
                    ,           SUM(c.Milliseconds)
                    ,           1

                    FROM        CTEIndividualTotals c
        )

        INSERT      #Temp
        (
                    [Database]
        ,           [Indexes]
        ,           [Statistics]
        ,           [TotalDuration]
        ,           [Time]
        )

        SELECT      c.[Database]
        ,           c.[Indexes]
        ,           c.[Statistics]
        ,           CONVERT(decimal(19, 3), c.[Milliseconds] / 1000.00)
        ,           CONVERT(time, DATEADD(MILLISECOND, c.[Milliseconds], 0))

        FROM        CTEResult c

        ORDER BY    [SortOrder]
        ,           [Database];
    END;

    -- convert temp table to html table
    SELECT  @xml =  CONVERT
                    (
                        nvarchar(MAX)
                    ,   (
                            SELECT  CASE t.[Database] WHEN N'Total' THEN @span_start + t.[Database] + @span_end ELSE t.[Database] END AS [td]
                            ,       N''
                            ,       N'right' AS [td/@align]
                            ,       CASE t.[Database] WHEN N'Total' THEN @span_start + CONVERT(nvarchar(10), t.[Indexes]) + @span_end ELSE CONVERT(nvarchar(10), t.[Indexes]) END AS [td]
                            ,       N''
                            ,       N'right' AS [td/@align]
                            ,       CASE t.[Database] WHEN N'Total' THEN @span_start + CONVERT(nvarchar(10), t.[Statistics]) + @span_end ELSE CONVERT(nvarchar(10), t.[Statistics]) END AS [td]
                            ,       N''
                            ,       N'right' AS [td/@align]
                            ,       CASE t.[Database] WHEN N'Total' THEN @span_start ELSE '' END
                                    + LEFT(CONVERT(nvarchar(50), t.[Time]), 2) + N'h ' + SUBSTRING(CONVERT(nvarchar(50), t.[Time]), 4, 2) + N'm ' + SUBSTRING(CONVERT(nvarchar(50), t.[Time]), 7, 6) + N's'
                                    + CASE t.[Database] WHEN N'Total' THEN @span_end ELSE '' END AS [td]

                            FROM    #Temp t

                            FOR XML PATH('tr')
                                ,   ELEMENTS
                        )
                    );

    -- combine the table rows from above into a complete html document
    SELECT  @body = N'<html><body><H3>Index Optimise Results for '
                    + @@SERVERNAME
                    + N' on '
                    + CONVERT(nvarchar(10), SYSDATETIME(), 120)
                    + N'</H3><table border = 1><tr><th> Database </th><th> Indexes </th><th> Statistics </th><th> Total Time </th></tr>'
                    + REPLACE(REPLACE(@xml, '&lt;', '<'), '&gt;', '>')
                    + N'</table></body></html>';

    -- get the email address of the operator
    SELECT  @email = o.email_address
    FROM    msdb.dbo.sysoperators o
    WHERE   o.name = @Operator;

    -- just in case the operator is non-existent
    SELECT  @email = ISNULL(@email, 'your.fallback.email.account@your.domain.com');

    /* Debug Block
    SELECT  *
    FROM    #Temp;

    SELECT  @Body AS Body
    ,       @email AS Email;
    --*/;

    -- send the email
    EXEC    msdb.dbo.sp_send_dbmail
            @profile_name = N'Database Mail Account'
    ,       @recipients = @email
    ,       @subject = @subj
    ,       @body = @body
    ,       @body_format = 'HTML';
END;
GO

If you have any questions about this, don't hesitate to ask!

Answer 2

  USE msdb
  EXEC sp_send_dbmail
  @profile_name = 'MailProfile1',  --you will need to create this profile in the Database Mail under Management
  @recipients = 'test@email.com',
  @subject = 'CLR Sproc Resultset',
  @body = 'Resultset is attached.',
  @execute_query_database = '[DatabaseName]',
  @query = 'exec [DatabaseName].[SchemaName].[CLRProcName]'  

Try this out, hopefully it should get what you need.