I am setting up a data driven subscription to send out an email which includes a report. The Sql for the data driven subscription outputs the EmailTo, Subject, Comment(Body) and some report parameters. My issue is with the Comment (Body) field which I would like to format with CR/LF to make the email body look neat and readable. I've tried many different permutations of inserting the CR/LF, From concatenating Char(13) + Char(10) in which the email does recognize the Cr/LF to concatenating binary 0x0D + 0x0A which after the first concatenation of binary nothing else can be concatenated. Here is the code I've tried:
~~~~~~~~
declare @.iEndDate datetime
set @.iEndDate = dbo.fn_today()
Declare @.Account_group int
Set @.Account_group = 999
/*
DECLARE @.mybin1 binary(5), @.mybin2 binary(5)
SET @.mybin1 = 0x0D
SET @.mybin2 = 0x0A
*/
Declare @.CrLf VarChar(5)
set @.CrLf = CHAR(13) + CHAR(10)
Declare @.Str1 VarChar(255)
Declare @.Str2 VarChar(255)
Declare @.Str3 VarChar(255)
Declare @.Str4 VarChar(255)
Declare @.Str5 VarChar(255)
Declare @.Str6 VarChar(255)
Declare @.Str7 VarChar(255)
Declare @.Str8 VarChar(255)
Declare @.Str9 VarChar(255)
Set @.Str1 = 'Good Morning,'
Set @.Str2 = 'We are exposed by '
Set @.Str3 = 'Attached please find the margin call detail.'
Set @.Str4 = 'Please let us know if you have any questions.'
Set @.Str5 = 'Thank you.'
Set @.Str6 = 'Please respond to:'
Set @.Str7 = ''
Set @.Str8 = ''
Set @.Str9 = ''
declare @.t table
(
EmailTo VarChar(255),
ReplyTo VarChar(255),
Subject VarChar(255),
Comment Text,
Account_group Int,
Trading_Account_Id Int,
Broker_Code_Ky VarChar(255),
EndDate Datetime
)
insert into @.t
select
EmailTo = '', -- isnull(bf.firm_email,''),
ReplyTo = '',
Subject = 'Margin Call Notice: - ' + Trading_account_name + ' - Exposure: ' + '$' + Convert(varchar(20), convert(Money, Mark_amount),1) ,
Comment = @.Str1 + @.CrLf + @.CrLf + @.Str2 + '$' + Convert(varchar(20), convert(Money, Mark_amount),1) + '.' + @.CrLf + @.CrLf + @.Str3 + @.CrLf + @.CrLf + @.Str4 + @.CrLf + @.CrLf + @.Str5 + @.CrLf + @.CrLf + @.Str6 + @.CrLf + @.Str7+ @.CrLf + @.Str8 + @.CrLf + @.Str9,
Account_group = @.Account_group,
Trading_Account_Id = trading_account_Id,
Broker_Code_Ky = EGS.Broker_Code_Ky,
EndDate = @.iEndDate
from fni_ExposureGovernmentSummary (@.iEndDate) EGS
join broker_group bg
on EGS.broker_code_ky = bg.broker_code_ky
Join broker_firm bf
on bg.fbe_firm_id = bf.broker_firm_id
Order by
Broker_Group_Name,
Trading_account_Name
select top 2 * from @.t
--~~~~~~~~~~~~~
The Email my company is using is Lotus Notes.
The Question is how do I code the the Comment field so that my email will recognize the Cr/LF.
Thanks
Elias
Try swapping the values - use Char(10) & Char(13) rather than Char(13) & Char(10).
Thanks.
