HTML Table Results For Email

• higgim

  SSCarpal Tunnel

  Points: 4090

  More actions

  February 9, 2018 at 6:45 pm

  #343594

  Comments posted to this topic are about the item HTML Table Results For Email

• kbaker91

  Valued Member

  Points: 52

  More actions

  February 19, 2018 at 7:55 am

  #1980272

  This was interesting and it generated an interesting result in my case.
  

  Excellent header, but strange rows.....
  Thanks!
  Ken
  kbaker20@humana.com

• ksiozios

  SSC Enthusiast

  Points: 149

  More actions

  February 19, 2018 at 1:40 pm

  #1980340

  A great way to do this!  One thing I noticed is that the consecutive REPLACE functions at the end of the SP don't really do anything (i.e. they replace '<' with itself).  I changed the code to this and it seemed to work: 
  ... + REPLACE(
            REPLACE(@BodyHTML, '&lt;', '<') ,
            '&gt;' ,
            '>') ...

  Note: I have not fully tested this so use at your own risk 🙂

  Thanks for the innovative approach 🙂

• jbeard-1143697

  SSC Rookie

  Points: 25

  More actions

  February 19, 2018 at 2:05 pm

  #1980345

  I suspect posting it replaced the &lt; and &gt; with the HTML replacements.  I also added a <TR> tag for the header which I believe is required for proper HTML form. I also noticed it's not fond of timestamp columns, but then again who is 🙂

      set @ColumnHeader
          = '<table cellpadding="2" cellspacing="2" border="1" style="font-family: Arial; font-size: 8pt"><TR>'
            + @ColumnHeader + '</TR>';

• hoolash

  Ten Centuries

  Points: 1258

  More actions

  February 20, 2018 at 1:03 am

  #1980390

  Thank you for this article. It works but the final HTML output is broken. I guess it has to do with the tags and since I am not well versed with HTML, I am having a hard time fixing this.

  Here is how it looks in my email message:

  

  Can anyone please help?

• higgim

  SSCarpal Tunnel

  Points: 4090

  More actions

  February 20, 2018 at 8:15 am

  #1980463

  hoolash - Tuesday, February 20, 2018 1:03 AM

  Thank you for this article. It works but the final HTML output is broken. I guess it has to do with the tags and since I am not well versed with HTML, I am having a hard time fixing this.

  Here is how it looks in my email message:

  

  Can anyone please help?

  Are you able to supply the table spec and some sample data?

• higgim

  SSCarpal Tunnel

  Points: 4090

  More actions

  February 20, 2018 at 8:17 am

  #1980467

  kbaker91 - Monday, February 19, 2018 7:55 AM

  This was interesting and it generated an interesting result in my case.
  

  Excellent header, but strange rows.....
  Thanks!
  Ken
  kbaker20@humana.com

  Would be keen to see the table and some sample data.  May have missed something.  Whilst no help to you, it works for the example we threw at it 🙂

• mar1jxm

  Ten Centuries

  Points: 1303

  More actions

  March 14, 2018 at 7:53 am

  #1983153

  I am having the same issue

  
  Here is the data for your review

  name    User_Login    enabled    date_created    date_modified    step_id    step_name    command
  Modification Test    US\mar1jxm    1    2018-03-12 11:25:16.573    2018-03-13 17:06:26.183    1    Step 1    Select 50 +30 +10
  Modification Test    US\mar1jxm    1    2018-03-12 11:25:16.573    2018-03-13 17:06:26.183    2    Step 2 Insert    Select 60
  Modification Test    US\mar1jxm    1    2018-03-12 11:25:16.573    2018-03-13 17:06:26.183    3    Step 3    Select 114

  
  Jacob Milter

• higgim

  SSCarpal Tunnel

  Points: 4090

  More actions

  March 14, 2018 at 9:18 am

  #1983191

  mar1jxm - Wednesday, March 14, 2018 7:53 AM

  I am having the same issue

  
  Here is the data for your review

  name    User_Login    enabled    date_created    date_modified    step_id    step_name    command
  Modification Test    US\mar1jxm    1    2018-03-12 11:25:16.573    2018-03-13 17:06:26.183    1    Step 1    Select 50 +30 +10
  Modification Test    US\mar1jxm    1    2018-03-12 11:25:16.573    2018-03-13 17:06:26.183    2    Step 2 Insert    Select 60
  Modification Test    US\mar1jxm    1    2018-03-12 11:25:16.573    2018-03-13 17:06:26.183    3    Step 3    Select 114

  Can you supply the table definition? As it work for me.

  
  CREATE TABLE emailtest
  (
   Name NVARCHAR(100) ,
   User_Login NVARCHAR(100) ,
   enabled BIT ,
   date_created DATETIME2 ,
   date_modified DATETIME2 ,
   step_id INT ,
   step_name NVARCHAR(100) ,
   command NVARCHAR(MAX)
  )

  INSERT INTO emailtest ( Name ,
        User_Login ,
        enabled ,
        date_created ,
        date_modified ,
        step_id ,
        step_name ,
        command )
VALUES
('Modification Test', 'US\mar1jxm', 1, '2018-03-12 11:25:16.573', '2018-03-13 17:06:26.183', 1 ,'Step 1', 'Select 50 +30 +10'),
('Modification Test','US\mar1jxm', 1, '2018-03-12 11:25:16.573', '2018-03-13 17:06:26.183', 2, 'Step 2', 'Insert Select 60'),
('Modification Test', 'US\mar1jxm', 1, '2018-03-12 11:25:16.573', '2018-03-13 17:06:26.183', 3, 'Step 3', 'Select 114' )

  DECLARE @GeneratedHTML NVARCHAR(MAX);
EXEC dbo.sp_GenerateHTMLTableResults @ResultsTableName = N'dbo.emailtest' ,     -- nvarchar(100)
               @GeneratedHTML = @GeneratedHTML OUTPUT -- nvarchar(max)

  PRINT @GeneratedHTML

• mar1jxm

  Ten Centuries

  Points: 1303

  More actions

  March 14, 2018 at 1:58 pm

  #1983290

  Thank you very much for your reply.
  I ran your script AS IS and got the same result.
  Any ideas?
  

  
  Jacob Milter

• mar1jxm

  Ten Centuries

  Points: 1303

  More actions

  March 14, 2018 at 2:13 pm

  #1983296

  
  Hi again!
  As a test I created a html file and opened it in IE.
  Same result!

  
  Jacob Milter