March 11, 2014 at 6:30 am
I am trying to format to paragraph of output to be used in an email, I have the static portion in a table
which i have set to body1
DECLARE @Body1 VARCHAR(MAX)
set @Body1 = (select top(1)EEO_TESTDATA.EEO_BODY + EEO_TESTDATA.EEO_UNIQUE from EEO_TESTDATA
where ID=@count)
After this paragraph i need be begin a new line and insert one more line. I have tried
DECLARE @crlf char(2) = Char(13) + Char(10)
set @Body1 = @Body1 + @crlf
as well as
set @Body1 = @Body1 + Char(13)+Char(10)
I then add the data that needs to appear on the new line:
set @Body2 = (@Body1 + (select top(1) EEO_TESTDATA.EEO_CREATEDBY from EEO_TESTDATA where ID=@count))
both result in the data being appended directly after the existing paragraph, not on a new line
Please connect to the form via the link below. Thank You http://yadayadayadayadaaspx?eeoid=383910919836XUPAUYHRDGIK this is the end of body1 I need a new line here CREATEDBY
Your suggestion is appreciated.
March 11, 2014 at 6:33 am
How do you test your output?
Did you use SELECT or PRINT?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 11, 2014 at 6:45 am
Yes i run the Proc and use dbmail(not select or print)
EXEC msdb.dbo.sp_send_dbmail
@profile_name='tets_profile',
@recipients=@Recipient_Email,
@subject='test_subject',
@body=@Body3,
@from_address='sendto me@myemail.com',
@body_format='HTML'
cooperation in submitting this information is important to ensure the accuracy of our information. Submission of the information is voluntary and refusal to provide it will not subject you to any adverse treatment. Please connect to the form via the link below. Thank You http://yadayadayada.com/www/eeoform.aspx?eeoid=383910919836XUPAUYHRDGIK createdby
where createdby should be on a new line....
March 11, 2014 at 6:48 am
The CHAR(10)+CHAR(13) does add a linefeed+return to the string, so it looks like you used a SELECT to return the results (like Koen mentioned in the previous post).
Btw: the following code will work in one single pass:
DECLARE @Body1 VARCHAR(MAX)
SET @Body1 = ''
select @Body1 = @Body1 + EEO_TESTDATA.EEO_BODY + EEO_TESTDATA.EEO_UNIQUE + CHAR(10) + CHAR(13)
from EEO_TESTDATA
where EEO_BODY is not null
and EEO_UNIQUE is not null
ORDER BY ID
PRINT @Body1 -- display in the messages tab of the results-pane
March 11, 2014 at 6:52 am
When you specify the body_format as HTML you also need to use HTML paragraph endings. Try using "<p> </p>" instead of the "CHAR(10)+CHAR(13)"...
March 11, 2014 at 7:12 am
</p>
did it!!!
Thank you Soooo much...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply