SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Building a Comma Separated on a new line


Building a Comma Separated on a new line

Author
Message
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10951 Visits: 7891
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Tava
    Tava
    Right there with Babe
    Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)

    Group: General Forum Members
    Points: 785 Visits: 806
    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...
    Tava
    Tava
    Right there with Babe
    Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)

    Group: General Forum Members
    Points: 785 Visits: 806
    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
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10951 Visits: 7891
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Tava
    Tava
    Right there with Babe
    Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)

    Group: General Forum Members
    Points: 785 Visits: 806
    sorry, IE crashed as i edited the reply Smile

    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?
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10951 Visits: 7891
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Tava
    Tava
    Right there with Babe
    Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)

    Group: General Forum Members
    Points: 785 Visits: 806
    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
    ...
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10951 Visits: 7891
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Tava
    Tava
    Right there with Babe
    Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)

    Group: General Forum Members
    Points: 785 Visits: 806
    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!!
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10951 Visits: 7891
    You are very welcome :-)

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search