October 8, 2013 at 5:06 pm
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);October 8, 2013 at 5:14 pm
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
...
October 8, 2013 at 5:23 pm
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);October 8, 2013 at 5:31 pm
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!!
October 8, 2013 at 5:34 pm
You are very welcome 🙂
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply