Practical usage of SQLCLR: Building QueryToHtml function

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Comments posted to this topic are about the item Practical usage of SQLCLR: Building QueryToHtml function

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4482

    Thank you Darko for this most excellent idea to build upon.
    This kind of article is why i love sqlservercentral so much !

  • Sergiy

    SSC Guru

    Points: 109671

    Once again - Thank you so much Microsoft for ditching sp_makewebtask.

    12 years on - people are still trying to reinvent it.

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    Well done. I appreciate the effort required to put an article like this together let alone the coding to build the tool itself.

    I know a guy that hangs on this site whose name rhymes with Reff Snowden who would appreciate a full-featured replacement for the web task sp. There is a reason why Microsoft dropped it, however. There are some other pieces of the language that are remnants of its client-server roots that I'd wish they'd drop as well.

    In a sense this brings back a popular tool but in a sense it's regressive in terms of where the platform and the industry are going.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • MgSam

    SSC Enthusiast

    Points: 102

    I made a similar proc that uses SQL but unlike the original query in this article, is completely dynamic.

    https://stackoverflow.com/a/29708178/1195036

  • the sqlist

    SSCrazy

    Points: 2398

    Here is a simple stored procedure that will do basically the same thing:
    CREATE PROCEDURE [dbo].[spHtmlFromTableEmail]
        @database varchar(100),
        @schema varchar(5)='dbo',
        @table_name varchar(100),
        @columns_list varchar(8000)='ALL',
        @subject varchar(100)='',
        @body varchar(max)='',
        @email_list varchar(1000),
        @cc_list varchar(1000)=NULL,
        @mail_profile varchar(100),
        @send char(1)='N'
    as

    declare
        @headers nvarchar(max)='',
        @content nvarchar(max)='',
        @sql nvarchar(max)=''

    if object_id(@database+'.'+@schema+'.'+@table_name) is null
    begin
        DECLARE @msg NVARCHAR(MAX);
        SET @msg = N'Table doesn''t exist: ' + @database+'.'+@schema+'.'+@table_name

        RAISERROR(@msg,16,1);
        return
    end

    -- if the columns list is empty or ALL then build the list from INFORMATION_SCHEMA
    if isnull(@columns_list,'ALL')='ALL'
    begin
        SET @columns_list=''
        select @sql=
    'select
        @content_out=@content_out+COLUMN_NAME+'',''
    from
        '+@database+'.INFORMATION_SCHEMA.COLUMNS
    where
        TABLE_NAME='''+@table_name+''''
        exec sp_executesql @sql, N'@content_out nvarchar(max)='''' OUTPUT', @content_out=@columns_list output
        select
            @sql='',
            @columns_list=left(@columns_list,len(@columns_list)-1)

    end
        
        /*-- ### debug purpsoe only
        print @sql
        print @columns_list
        return    
        --*/

    -- buld the HTML header and the sql for the table content    
    select
        @headers=@headers+char(9)+'<th> '+ cols.Elem+ ' </th>'+char(13)+char(10),
        @sql=@sql+char(13)+char(10)+char(9)+'ISNULL(cast(['+cols.Elem+'] as varchar(max)),'''') as '''+case when cols.Elem='row_css_class' and cols.ElemID=1 then cols.Elem else 'td' end +''','''','
    FROM
        [zb_dba_maint].[dbo].fnParseStringToSet(@columns_list,',') cols

    select
         @headers='<tr>'+char(13)+char(10)+@headers+'</tr>'+char(13)+char(10),
         @sql='SELECT '+left(@sql,len(@sql)-1)+char(13)+char(10)+
         'FROM '+char(13)+char(10)+char(9)+@database+'.'+@schema+'.'+@table_name+char(13)+char(10)+
         'FOR XML PATH(''tr''), ELEMENTS'
    select
        @sql='SET @content_out =
    (
    SELECT
        replace(
            replace(
                replace(
                    replace(
                        replace(
                            replace(
                                replace(tbl,''<tr><row_css_class>Monday</row_css_class>'',''<tr class="monday">'')
                            ,''<tr><row_css_class>Tuesday</row_css_class>'',''<tr class="tuesday">'')
                        ,''<tr><row_css_class>Wednesday</row_css_class>'',''<tr class="wednesday">'')
                    ,''<tr><row_css_class>Thursday</row_css_class>'',''<tr class="thursday">'')
                ,''<tr><row_css_class>Friday</row_css_class>'',''<tr class="friday">'')
            ,''<tr><row_css_class>Saturday</row_css_class>'',''<tr class="saturday">'')
        ,''<tr><row_css_class>Sunday</row_css_class>'',''<tr class="sunday"'') as htm
    from
    (
    SELECT CAST(('+@sql+') AS VARCHAR(MAX))
    ) q (tbl))
    '
    --select @sql
    --return
    exec sp_executesql @sql, N'@content_out nvarchar(max) OUTPUT', @content_out=@content output

    select
        @body='
    <html>
    <head>
    <STYLE TYPE="text/css">
    <!--
    BODY
     {
     font-family:tahoma;
     font-size:11;
     }
    table, td, th {
     margin: 1;
     padding: 1;
     border: 1px solid gray; 
     }
    table {
     border-collapse: collapse;
     border-spacing: 1;
     }
    TD
     {
     color:black;
     }
    TH
     {
     background-color:#3390CC;
     color:white;
     align=left;
     }
    TR.monday
        {
        background-color: #CCFFFF; color: black;
        }
    TR.tuesday
        {
        background-color: #FFCCFF; color: black;
        }
    TR.wednesday
        {
        background-color: #CCFF99; color: black;
        }
    TR.thursday
        {
        background-color: #FFCC66; color: black;
        }
    TR.friday
        {
        background-color: #CCFFCC; color: black;
        }
    TR.saturday
        {
        background-color: #FFCCCC; color: black;
        }
    TR.sunday
        {
        background-color: #99CCFF; color: black;
        }
    A:link{color:blue}
    A:visited{color:brown}
    -->
    </STYLE>
    <head>
    <body>
        <h5>'+ISNULL(@body,'')+'</h5>
        <h5>Source: '+convert(varchar, SERVERPROPERTY('ServerName'))+'.'+db_name()+'.'+@schema+'.'+@table_name+' content '+cast(GETDATE()as varchar(12))+'</h5>
        <table border="1">
    '+replace(@headers,'<th> row_css_class </th>','')+@content+'
        </table>
    </body>
    </html>
    '
    if @subject like '%ERROR%'
        set @body=replace(@body,'background-color:#3390CC;','background-color:red;')
        if @send='Y'
        begin
            select
                @subject=ISNULL(@subject,convert(varchar, SERVERPROPERTY('ServerName'))+' Table '+db_name()+'.'+@schema+'.'+@table_name+' content '+cast(GETDATE()as varchar(12)))
            EXECUTE msdb.dbo.sp_send_dbmail
                @recipients=@email_list,
                @copy_recipients=@cc_list,
                @body_format = 'HTML',
                @body=@body,
                @subject =@subject,
                @profile_name =@mail_profile
        end
        else
            select replace(replace(replace(replace(
                    @body,    '<tr>',char(13)+char(10)+char(9)+char(9)+'<tr>'),
                            '</tr>',char(13)+char(10)+char(9)+char(9)+'</tr>'),
                            '<td>',char(13)+char(10)+char(9)+char(9)+char(9)+'<td>'),
                            '<th>',char(9)+char(9)+'<th>')

    If you have any query you can use SELECT ... INTO <table_name> feature and then the use the stored procedure above to send an email in HTML format with the <table_name> table just created. After that you can delete the table. in the @columns_list parameter you pass a list of the columns you want to build the result separated by comma (,). The default for this parameter is 'ALL', which will use all the columns.

    -- Update June 6th.
    As someone noticed, the stored procedure above uses some functions that I forgot to provide. Here they are:

    CREATE FUNCTION [dbo].[fnTally]()
    RETURNS TABLE --WITH SCHEMABINDING
    AS
    /*******************************************************************************\
    Function  : fnTally

    Purpose   : returns a set with numbers from 1 to 10,000
         to be used in parsing and sequential data generation whithout loop
        
    Parameters  : no parameters

    Invoke   :
     
       select * from [dbo].[fnTally]()
       select N from [dbo].[fnTally]()
       select substring('abcdef',N,1) as chr from [dbo].[fnTally]() where N<len('abcdef') -- parsing a string
       select dateadd(dd, N, '2007-01-01') as dte from [dbo].[fnTally]() --gets dates for about 30 years

    Author   : AdrianBT - 2013-03-18  
    \*******************************************************************************/
    RETURN
      WITH
      E1(N) AS
      ( --10E+1 or 10 rows
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
      ),   
    E2(N) AS
    ( --10E+2 or 100 rows 
       SELECT 1 FROM E1 a, E1 b
      ),
    E4(N) AS
    ( --10E+4 or 10,000 rows max
       SELECT 1 FROM E2 a, E2 b
      )
         SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N FROM E4
      ;
    GO



    CREATE FUNCTION [dbo].[fnParseStringToSet]
    (
      @str VARCHAR(8000),
      @sep CHAR(1)
    )
    --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT KILLS PERFORMANCE!
    RETURNS TABLE
    AS
    /*******************************************************************************\
    Function  : fnParseStringToSet

    Purpose   : parses a string by a separator and returns a two columns set with
          each element and its position; uses the fnTally for parsing
        
    Parameters  : @str - the string to parse
         @sep - the separator character

    Invoke   :
     
       select * from [dbo].[fnParseStringToSet]('ab cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef',' ')

       select tsk.ElemId, tsk.Elem as QATaskId,tme.Elem as EndTime from
        [dbo].[fnParseStringToSet]('10,20,30,40,50',',') tsk
        left join [dbo].[fnParseStringToSet]('17:10,17:11,17:12',',') tme
          on tme.ElemId=tsk.ElemId

    Author   : AdrianBT - 2013-03-18  
    \*******************************************************************************/
    RETURN
      with cteStart(N1) AS
      (--returns N+1 (starting position of each "element" just once for each delimiter)
       SELECT 0 UNION ALL
       SELECT t.N+1 FROM [dbo].[fnTally]() t WHERE SUBSTRING(@str,t.N,1) = @sep and t.N<=len(@str)
      ),
      cteLen(N1,L1) AS
      (--returns start and length (for use in substring)
       SELECT s.N1,
          ISNULL(NULLIF(CHARINDEX(@sep,@str,s.N1),0)-s.N1,8000)
       FROM cteStart s
      )
      --so the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
      SELECT
       ROW_NUMBER() OVER(ORDER BY l.N1)  as ElemID,
       SUBSTRING(@str, l.N1, l.L1)    as Elem
      FROM
       cteLen l
      ;


    GO


    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • andycadley

    SSCertifiable

    Points: 5291

    I wrote something similar in my current job, but using purely dynamic SQL and using a table (in any database, including temp tables) as the source rather than a query which avoids most of the issues with parameters etc (then a colleague did the Query-> Table->HTML on top of mine for dealing with most of the quick cases. Over time it's picked up some handy functions for filtering both rows and columns as well as highlighting data that matches key conditions. It would be nice to get something like this in SQL Server itself though, it's such a common thing to require.

    On a slightly different note, I'd suggest not using Excel interop on your SQL box like that. Excel isn't really built for non-interactive use and if you aren't careful is very likely to pop up a dialog box that nobody will ever see, blocking your SQL whilst it waits for interaction. It's better to either generate Excel XML directly or go via the Office File Format DLLs, which are much more robust in that matter (personally I tend to just go straight to SSIS at that point).

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Theo Ekelmans - Monday, June 5, 2017 1:20 AM

    Thank you Darko for this most excellent idea to build upon.
    This kind of article is why i love sqlservercentral so much !

    Thank you Theo. Negative comments are welcome too.

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Orlando Colamatteo - Monday, June 5, 2017 6:40 AM

    Well done. I appreciate the effort required to put an article like this together let alone the coding to build the tool itself.I know a guy that hangs on this site whose name rhymes with Reff Snowden who would appreciate a full-featured replacement for the web task sp. There is a reason why Microsoft dropped it, however. There are some other pieces of the language that are remnants of its client-server roots that I'd wish they'd drop as well.In a sense this brings back a popular tool but in a sense it's regressive in terms of where the platform and the industry are going.

    Thank you Orlando 🙂 ! You said everything exactly. I have nothing to add. Thanks for the engagement.

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Sergiy - Monday, June 5, 2017 5:50 AM

    Once again - Thank you so much Microsoft for ditching sp_makewebtask.

    12 years on - people are still trying to reinvent it.

    Thank you Sergiy for your response 🙂!
    Something like that. By the way, we are trying to detect the SQLCLR that has been there for so many years and is not so popular.

  • Darko Martinović

    Hall of Fame

    Points: 3535

    In the introduction I forgot to mention that dynamic T-SQL is one of the methods to get valid HTML.
    My opinion is that string manipulation is not a T-SQL advantage. This is the area where .NET takes precedence.
    I am convinced that all these solutions are presented by colleagues through dynamic T-SQL's good.
    But I will repeat it is not the topic of this article, nor would it ever be my choice.

  • DLight101

    SSC Rookie

    Points: 26

    What is "[zb_dba_maint].[dbo].fnParseStringToSet(@columns_list,',') " and where is that coming from?
    Thank you

  • the sqlist

    SSCrazy

    Points: 2398

    DLight101 - Tuesday, June 6, 2017 8:08 AM

    What is "[zb_dba_maint].[dbo].fnParseStringToSet(@columns_list,',') " and where is that coming from?
    Thank you

    🙂 Sorry about that. It is a function I built which takes a string and a separator character, parse the string and returns a set with all the values separated by the separator. This function on its turn uses another function, which I will both post below. You should create these functions in the same database you have the spHtmlFromTableEmail stored procedure and than remove the database name reference inside it:

    CREATE FUNCTION [dbo].[fnTally]()
    RETURNS TABLE --WITH SCHEMABINDING
    AS
    /*******************************************************************************\
    Function    : fnTally

    Purpose        : returns a set with numbers from 1 to 10,000
                 to be used in parsing and sequential data generation whithout loop
                
    Parameters    : no parameters

    Invoke        :
        
            select * from [dbo].[fnTally]()
            select N from [dbo].[fnTally]()
            select substring('abcdef',N,1) as chr from [dbo].[fnTally]() where N<len('abcdef') -- parsing a string
            select dateadd(dd, N, '2007-01-01') as dte from [dbo].[fnTally]() --gets dates for about 30 years

    Author        : AdrianBT - 2013-03-18        
    \*******************************************************************************/
    RETURN
        WITH
        E1(N) AS
        ( --10E+1 or 10 rows
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
        ),        
     E2(N) AS
     ( --10E+2 or 100 rows    
            SELECT 1 FROM E1 a, E1 b
        ),
     E4(N) AS
     ( --10E+4 or 10,000 rows max
            SELECT 1 FROM E2 a, E2 b
        )
                 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N FROM E4
        ;
    GO

    CREATE FUNCTION [dbo].[fnParseStringToSet]
    (
        @str VARCHAR(8000),
        @sep CHAR(1)
    )
    --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT KILLS PERFORMANCE!
    RETURNS TABLE
    AS
    /*******************************************************************************\
    Function    : fnParseStringToSet

    Purpose        : parses a string by a separator and returns a two columns set with
                    each element and its position; uses the fnTally for parsing
                
    Parameters    : @STR - the string to parse
                 @sep - the separator character

    Invoke        :
        
            select * from [dbo].[fnParseStringToSet]('ab cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef',' ')

            select tsk.ElemId, tsk.Elem as QATaskId,tme.Elem as EndTime from
                [dbo].[fnParseStringToSet]('10,20,30,40,50',',') tsk
                left join [dbo].[fnParseStringToSet]('17:10,17:11,17:12',',') tme
                    on tme.ElemId=tsk.ElemId

    Author        : AdrianBT - 2013-03-18        
    \*******************************************************************************/
    RETURN
        with cteStart(N1) AS
        (--returns N+1 (starting position of each "element" just once for each delimiter)
            SELECT 0 UNION ALL
            SELECT t.N+1 FROM [dbo].[fnTally]() t WHERE SUBSTRING(@str,t.N,1) = @sep and t.N<=len(@str)
        ),
        cteLen(N1,L1) AS
        (--returns start and length (for use in substring)
             SELECT s.N1,
                    ISNULL(NULLIF(CHARINDEX(@sep,@str,s.N1),0)-s.N1,8000)
             FROM cteStart s
        )
        --so the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
        SELECT
            ROW_NUMBER() OVER(ORDER BY l.N1)    as ElemID,
            SUBSTRING(@str, l.N1, l.L1)            as Elem
        FROM
            cteLen l
        ;

    GO

    Now you will use for the example you posted:

    [dbo].fnParseStringToSet(@columns_list,',')

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • the sqlist

    SSCrazy

    Points: 2398

    Darko Martinovic - Tuesday, June 6, 2017 3:21 AM

    In the introduction I forgot to mention that dynamic T-SQL is one of the methods to get valid HTML.
    My opinion is that string manipulation is not a T-SQL advantage. This is the area where .NET takes precedence.
    I am convinced that all these solutions are presented by colleagues through dynamic T-SQL's good.
    But I will repeat it is not the topic of this article, nor would it ever be my choice.

    We understand that, with the reserve that string manipulation in SQL server is not a problem at all, but at the same time I think people deserve to know that the same things can be achieved in a pure SQL approach, which is also much, much simpler than using CLR feature. If, however, the things become over complicated the CLR method might be preferable although in that case one should leave altogether the SQL Server environmant and bult an external module using a programming language of their choice.

     As a general idea the CLR processes are using a lot of memory and CPU power, which in some cases are in the detriment of performace.

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • Darko Martinović

    Hall of Fame

    Points: 3535

    the sqlist - Tuesday, June 6, 2017 8:34 AM

    Darko Martinovic - Tuesday, June 6, 2017 3:21 AM

    In the introduction I forgot to mention that dynamic T-SQL is one of the methods to get valid HTML.
    My opinion is that string manipulation is not a T-SQL advantage. This is the area where .NET takes precedence.
    I am convinced that all these solutions are presented by colleagues through dynamic T-SQL's good.
    But I will repeat it is not the topic of this article, nor would it ever be my choice.

    We understand that, with the reserve that string manipulation in SQL server is not a problem at all, but at the same time I think people deserve to know that the same things can be achieved in a pure SQL approach, which is also much, much simpler than using CLR feature. If, however, the things become over complicated the CLR method might be preferable although in that case one should leave altogether the SQL Server environmant and bult an external module using a programming language of their choice.

     As a general idea the CLR processes are using a lot of memory and CPU power, which in some cases are in the detriment of performace.

    You would be surprised! In many situation, spetially when parallel execution has involved, CLR performs better.
    I'm using QueryToHtml function during years and it performs well, it is easy to use and adopt.
    When you are in house DBA, probably you will not use CLR. But, if you are software vendor with thousend of customers and thousend of installations, CLR is much better solution.

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply