Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Building a Comma Separated on a new line Expand / Collapse
Author
Message
Posted Tuesday, October 8, 2013 4:18 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 1,805, Visits: 5,872
I can only think there was a problem with the way you tried?

Perhaps if you generate some sample html and post it here we can see what is wrong?


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1502863
    Posted Tuesday, October 8, 2013 4:26 PM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Sunday, April 27, 2014 8:38 PM
    Points: 90, Visits: 420
    mister.magoo (10/8/2013)
    I can only think there was a problem with the way you tried?

    Perhaps if you generate some sample html and post it here we can see what is wrong?


    This is my HTML template

    <html>
    <head>
    </head>
    <body>
    <p style="font-family: arial, verdana, times; font-size: 12px; color: #000000;">Original Zip Filename.<br/><br/>[[FileName]]<br/><br/>Missing PDF File.<br/><br/>[[PDF_FileName]]
    </p>
    </body>
    </html>


    This is my HTML Code, [[PDF_FileName]] is replaced with the string...
    Post #1502864
    Posted Tuesday, October 8, 2013 4:31 PM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Sunday, April 27, 2014 8:38 PM
    Points: 90, Visits: 420
    by putting the '< br/ >' (spaces were removed) it generates this

    <html><head></head><body><p style="font-family: arial, verdana, times; font-size: 12px; color: #000000;">Original File.<br/><br/>test.zip<br/><br/>PDF Filename.<br/><br/> abc123.pdf<br/>, test.pdf<br/>, test123.pdf<<br/></p></body></html>


    when i execute it i can see now its got weird characters " & lt ; br / & gt ; "


    edit: had to put spaces in otherwise it was correctly showing br br
    Post #1502869
    Posted Tuesday, October 8, 2013 4:33 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 9:10 AM
    Points: 1,805, Visits: 5,872
    When you replace [[PDF_FileName]] does the rendered html have <br/> between each file name?

    Can we see the final html (redacted)?

    edit:

    I see you have posted further ...

    Get the html right and it will work


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1502870
    Posted Tuesday, October 8, 2013 4:37 PM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Sunday, April 27, 2014 8:38 PM
    Points: 90, Visits: 420
    sorry, IE crashed as i edited the reply :)

    ummm, when i select my variable i get the wierd characters etc.... but when the email generates it shows BR.

    how can i pass through BR if it keeps getting translated?
    Post #1502872
    Posted Tuesday, October 8, 2013 5:06 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 9:10 AM
    Points: 1,805, Visits: 5,872
    It's hard to help because we can't see the whole process as you can...

    Can you try this to see if it works?(replace the profile and email address and fix the BR tags)

    exec msdb.dbo.sp_send_dbmail 
    @profile_name='YOUR PROFILE',
    @recipients='someone@email.com',
    @subject='test html',
    @body='test1.pdf< br/ >test2.pdf< br/ >',
    @body_format='HTML'



    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1502876
    Posted Tuesday, October 8, 2013 5:14 PM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Sunday, April 27, 2014 8:38 PM
    Points: 90, Visits: 420
    mister.magoo (10/8/2013)
    It's hard to help because we can't see the whole process as you can...

    Can you try this to see if it works?(replace the profile and email address and fix the BR tags)

    exec msdb.dbo.sp_send_dbmail 
    @profile_name='YOUR PROFILE',
    @recipients='someone@email.com',
    @subject='test html',
    @body='test1.pdf< br/ >test2.pdf< br/ >',
    @body_format='HTML'



    Fixed it!! might not be the right way but done it.


    In my variable @PDFFileName, i pass through '< br/ >' as normal.

    DECLARE @PDFFileName VARCHAR(8000) = (SELECT STUFF(( SELECT ' ' + PDF_FileName + '.pdf' + '<br/>' FROM @PDFResult ORDER BY 1 ASC FOR XML PATH('')),1,1,'')) 


    As the data gets translated from BR to the & l t ; b r / & g t ; etc, I do an additional replace to remove those characters with the BR i expect.

    SET @BodyHTML = 
    (
    SELECT
    [HTML] = REPLACE(REPLACE(REPLACE(HTML,'[[FileName]]',@ProcessingFile),'[[PDF_FileName]]',@PDFFileName),' & l t ; b r / & g t ; ','<br/>') -- sorry for spaces but its changing
    FROM
    @temptable
    )

    Returned the results as i hoped for
    1.pdf
    2.pdf
    3.pdf
    ...
    Post #1502877
    Posted Tuesday, October 8, 2013 5:23 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 9:10 AM
    Points: 1,805, Visits: 5,872
    I see your problem now - it is the tags in the XML ...

    try this instead:

    DECLARE @PDFFileName VARCHAR(8000) = 
    (
    SELECT ( SELECT PDF_FileName + '.pdf< br/ >'
    FROM @PDFResult
    ORDER BY 1 ASC FOR XML PATH(''),TYPE).value('.','varchar(8000)')
    )

    The addition of the TYPE directive and the subsequent ".value" method will prevent the entization of the < br /> tags.


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1502880
    Posted Tuesday, October 8, 2013 5:31 PM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Sunday, April 27, 2014 8:38 PM
    Points: 90, Visits: 420
    mister.magoo (10/8/2013)
    I see your problem now - it is the tags in the XML ...

    try this instead:

    DECLARE @PDFFileName VARCHAR(8000) = 
    (
    SELECT ( SELECT PDF_FileName + '.pdf< br/ >'
    FROM @PDFResult
    ORDER BY 1 ASC FOR XML PATH(''),TYPE).value('.','varchar(8000)')
    )

    The addition of the TYPE directive and the subsequent ".value" method will prevent the entization of the < br /> tags.



    GOT IT!!! both methods worked yours and mine, but yours is a cleaner read....

    I didnt work out it was in the HTML till you pointed me in the right direction before about issue being with HTML... mucked around a bit and saw that didnt matter what was being passed it ignored. Yeap the Tags kept getting translated but didnt know what to do with them... everytime i posted it here just kept changing it...

    appreciate your help on this!!

    Post #1502882
    Posted Tuesday, October 8, 2013 5:34 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 9:10 AM
    Points: 1,805, Visits: 5,872
    You are very welcome

    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1502883
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse