locked
Save Query Results To HTML File RRS feed

  • Question

  • Good morning.  I have a Query that runs every hour on my local SQL Server Via SQL Server Management Studio (V18.2).  I would like to know if there is code that would take the results of my QUERY and Save it to a local Drive as an HTML File?  I want the HTML File To Be Formatted so that I can E-mail it out.  I Already have Code to generate the E-Maill.  I just need help to take the SQL Query Results, format it to HTML and Save to a local Path (Example of Path "E:\Performance\Results.html")  Is this possible?  Can you please provide some examples?  Thank you in advance for your help :)

    SELECT 
    SubString([Route],1,1) As Sort
    ,Max(Case 
    When SubString([Route],1,1) = ''1'' THen ''Sunday''
    When SubString([Route],1,1) = ''2'' THen ''Monday''
    When SubString([Route],1,1) = ''3'' THen ''Tuesday''
    When SubString([Route],1,1) = ''4'' THen ''Wednesday''
    When SubString([Route],1,1) = ''5'' THen ''Thursday''
    When SubString([Route],1,1) = ''6'' THen ''Friday''
    When SubString([Route],1,1) = ''7'' THen ''Saturday''
    End) As DayOfWeek

         
          ,Case When [Pick_Area] = ''O'' Then ''Automation''
    When [Pick_Area] = ''C'' Then ''CPS'' End As Area
          ,Format(Sum([Tot_Units_Ordered]), ''###,###,###'') As Tot_Units_Ordered
          ,Format(Sum([Tot_Units_Complete]), ''###,###,###'') As Tot_Units_Complete
      ,Format(Sum([Tot_Units_Ordered]) - (Sum([Tot_Units_Complete]) + Sum([Scratches]) + Sum([BuyerScratch])), ''###,###,###'') As Tot_Units_Left
          ,Format(Sum([CasePick_Ordered]), ''###,###,###'') As CasePick_Ordered
          ,Format(Sum([CasePick_Complete]), ''###,###,###'') As CasePick_Complete
      ,Format(Sum([CasePick_Ordered]) - Sum([CasePick_Complete]), ''###,###,###'') As CasePick_Units_Left
          ,Format(Sum([Scratches]), ''###,###,###'') As Scratches
          ,Format(Sum([BuyerScratch]), ''###,###,###'') As BuyerScratches
          ,Format(Sum([PreAdjusted_Units]), ''###,###,###'') As PreAdjusted_Units
         
      FROM [WIN_Stanton].[dbo].[Picking_Progress_Rpt]
      Where Tot_Units_Ordered > 0
      Group By
    SubString([Route],1,1),[Pick_Area]

     

    Paul

    Tuesday, April 7, 2020 12:15 PM

Answers

  • Tale a look at

    http://bradsruminations.blogspot.com/2009/11/xml-paths-of-glory.html


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 7, 2020 2:29 PM
  • Hello Paul,

    I find this query, you can try:

    -- Description: Turns a query into a formatted HTML table. Useful for emails. 
    -- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
    -- =============================================
    CREATE PROC [dbo].[spQueryToHtmlTable] 
    (
      @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
      @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
      @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
    )
    AS
    BEGIN   
      SET NOCOUNT ON;
    
      IF @orderBy IS NULL BEGIN
        SET @orderBy = ''  
      END
    
      SET @orderBy = REPLACE(@orderBy, '''', '''''');
    
      DECLARE @realQuery nvarchar(MAX) = '
        DECLARE @headerRow nvarchar(MAX);
        DECLARE @cols nvarchar(MAX);    
    
        SELECT * INTO #dynSql FROM (' + @query + ') sub;
    
        SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
        FROM tempdb.sys.columns 
        WHERE object_id = object_id(''tempdb..#dynSql'')
        ORDER BY column_id;
    
        SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    
    
        EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT
    
        SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
        FROM tempdb.sys.columns 
        WHERE object_id = object_id(''tempdb..#dynSql'')
        ORDER BY column_id;
    
        SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';
    
        SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
        ';
    
      EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
    END
    GO

    Usage:

    DECLARE @html nvarchar(MAX);
    EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'SELECT * FROM dbo.People', @orderBy = N'ORDER BY FirstName';
    
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Foo',
        @recipients = 'bar@baz.com;',
        @subject = 'HTML email',
        @body = @html,
        @body_format = 'HTML',
        @query_no_truncate = 1,
        @attach_query_result_as_file = 0;

    More detail, you can refer to this thread Convert a SQL query result table to an HTML table for email

    Hope it will help.

    Best Regards

    Dawn


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 8, 2020 2:44 AM
  • Hello ,
    Have you solved the question?
    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members finding and reading the thread easily. 
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Paul 2 U Friday, April 10, 2020 12:31 PM
    Friday, April 10, 2020 8:56 AM

All replies

  • Tale a look at

    http://bradsruminations.blogspot.com/2009/11/xml-paths-of-glory.html


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 7, 2020 2:29 PM
  • Hello Paul,

    I find this query, you can try:

    -- Description: Turns a query into a formatted HTML table. Useful for emails. 
    -- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
    -- =============================================
    CREATE PROC [dbo].[spQueryToHtmlTable] 
    (
      @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
      @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
      @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
    )
    AS
    BEGIN   
      SET NOCOUNT ON;
    
      IF @orderBy IS NULL BEGIN
        SET @orderBy = ''  
      END
    
      SET @orderBy = REPLACE(@orderBy, '''', '''''');
    
      DECLARE @realQuery nvarchar(MAX) = '
        DECLARE @headerRow nvarchar(MAX);
        DECLARE @cols nvarchar(MAX);    
    
        SELECT * INTO #dynSql FROM (' + @query + ') sub;
    
        SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
        FROM tempdb.sys.columns 
        WHERE object_id = object_id(''tempdb..#dynSql'')
        ORDER BY column_id;
    
        SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    
    
        EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT
    
        SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
        FROM tempdb.sys.columns 
        WHERE object_id = object_id(''tempdb..#dynSql'')
        ORDER BY column_id;
    
        SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';
    
        SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
        ';
    
      EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
    END
    GO

    Usage:

    DECLARE @html nvarchar(MAX);
    EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'SELECT * FROM dbo.People', @orderBy = N'ORDER BY FirstName';
    
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Foo',
        @recipients = 'bar@baz.com;',
        @subject = 'HTML email',
        @body = @html,
        @body_format = 'HTML',
        @query_no_truncate = 1,
        @attach_query_result_as_file = 0;

    More detail, you can refer to this thread Convert a SQL query result table to an HTML table for email

    Hope it will help.

    Best Regards

    Dawn


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 8, 2020 2:44 AM
  • Hello ,
    Have you solved the question?
    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members finding and reading the thread easily. 
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Paul 2 U Friday, April 10, 2020 12:31 PM
    Friday, April 10, 2020 8:56 AM