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

Carriage return in xp_sendmail Expand / Collapse
Author
Message
Posted Thursday, June 3, 2010 10:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 3:18 PM
Points: 4, Visits: 32
Hello,

I'm running into a very annoying issue that I haven't been able to find any information about. I have set up a trigger on a table that sends out notices via xp_sendmail. In order to have the email show up clean, I have added char(13)+char(10) between select results on the email. However, whenever @Subject is more than 32 characters the carriage return/ line break character doesn't seem to work.

Anybody have an idea of what could be going on here?

I'm using SQL Server 2000 and here is shortened version of the trigger I'm currently using:

CREATE TRIGGER dbo.StageChanged ON CM.dbo.tLEAD AFTER UPDATE
AS
SET NOCOUNT ON
DECLARE @Stage nvarchar(50)
DECLARE @Subject nvarchar(100)
DECLARE @ProjectNumber nvarchar(15)
DECLARE @GP nvarchar(15)
DECLARE @Job nvarchar(4000)

SELECT
@Subject = l.Subject,
@ProjectNumber =ISNULL(pn.ProjectNumber, 'blank'),
@GP= CONVERT(varchar(15),CONVERT(money, ISNULL(tc.TotalCost,0))-CONVERT(money,ISNULL(sc.Subcontracted,0)),1),
@Stage=s.LeadStageName
FROM CM.dbo.tLead l (nolock)
LEFT JOIN
(SELECT MAX(pn2.FieldValue) As [ProjectNumber] , pn2.ObjectFieldSetKey
FROM
CM.dbo.tFieldValue pn2 (nolock)
INNER JOIN CM.dbo.tFieldDef fd2 (nolock) ON fd2.FieldDefKey=pn2.FieldDefKey
WHERE fd2.FieldName= 'Opportunity_UD_Project_Number'
GROUP BY pn2.ObjectFieldSetKey) pn
ON l.CustomFieldKey=pn.ObjectFieldSetKey
LEFT JOIN
(SELECT MAX(tc2.FieldValue) As [TotalCost] , tc2.ObjectFieldSetKey
FROM
CM.dbo.tFieldValue tc2 (nolock)
INNER JOIN CM.dbo.tFieldDef fd5 (nolock) ON fd5.FieldDefKey=tc2.FieldDefKey
WHERE fd5.FieldName= 'Opportunity_UD_Client_total'
GROUP BY tc2.ObjectFieldSetKey) tc
ON l.CustomFieldKey=tc.ObjectFieldSetKey
LEFT JOIN
(SELECT MAX(sc2.FieldValue) As [Subcontracted] , sc2.ObjectFieldSetKey
FROM
CM.dbo.tFieldValue sc2 (nolock)
INNER JOIN CM.dbo.tFieldDef fd6 (nolock) ON fd6.FieldDefKey=sc2.FieldDefKey
WHERE fd6.FieldName= 'Opportunity_UD_Subcontracted'
GROUP BY sc2.ObjectFieldSetKey) sc
ON l.CustomFieldKey=sc.ObjectFieldSetKey
INNER JOIN CM.dbo.tLeadStage s ON l.LeadStageKey= s.LeadStageKey
INNER JOIN INSERTED i (nolock) ON i.LeadKey=l.LeadKey

Set @Job = 'The following opportunity has been changed to '+ @Stage+': '+ char(13)+ char(10)+'Subject: '+ @Subject + char(13) + char(10)+'Project Number: '+@ProjectNumber +char(13)+char(10)+ 'Estimated GP: '+@GP

If Update(LeadStageKey)
BEGIN
If EXISTS (SELECT i.LeadStageKey FROM INSERTED i INNER JOIN DELETED d ON d.LeadKey=i.LeadKey
WHERE
( i.LeadStageKey<>d.LeadStageKey AND i.LeadStageKey=867)
)
BEGIN
EXEC master.dbo.xp_sendmail @recipients = 'jcantlon@bighrm.com', @subject = 'It''s a job!', @message = @Job
END
END


Within the Set @Job statement above, when @Subject is greater than 32 characters, the email is sent with @Subject and @Project on the same line, without the expected line break.

Any suggestions are appreciated!

Thanks,

John
Post #932280
Posted Thursday, June 3, 2010 10:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 12,916, Visits: 32,077
i bet the email is sent as html, and since html does not respect CrLf/Lf, you should substitute the <br /> instead:
Set @Job = 'The following opportunity has been changed to ' + @Stage + ': '
+ '<br />'
+'Subject: '+ @Subject
+ '<br />'
+'Project Number: '+ @ProjectNumber
+ '<br />'
+ 'Estimated GP: '+ @GP




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #932294
Posted Thursday, June 3, 2010 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 3:18 PM
Points: 4, Visits: 32
Thanks for the reply, Lowell.

I tried your suggetion, but it just resulted in <br /> showing up in my email.

All of the other Char(13)+Char(10) formulas work to correctly format the email with carriage returns, it is only when @Subject is over 32 characters that the issue comes up and the line break no longer shows up.
Post #932299
Posted Thursday, June 3, 2010 12:09 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:31 AM
Points: 419, Visits: 725
From my experience I don't believe it is possible to insert a newline in a SQL email. I ran into this in the past. I just had to tell my team that it was a physical limitation, and they had to keep the message to a single paragraph. Unfortunate, but workable. If you find a solution, I'm all ears, but I did a ton of looking, and I don't think it can be done at all.

--J
Post #932332
Posted Tuesday, June 15, 2010 6:43 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:10 PM
Points: 4,576, Visits: 8,349
jcantlon (6/3/2010)
Thanks for the reply, Lowell.

I tried your suggetion, but it just resulted in <br /> showing up in my email.


You need to start the string with <HTML> and end it with </HTML>
Post #937429
Posted Wednesday, June 16, 2010 6:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 428, Visits: 956
Thanks Sergiy!

It works (at least with xp_smtp_sendmail)!!!

Been trying to get CR-LF in emails for years!!!!



Post #938104
Posted Wednesday, June 16, 2010 12:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 3:18 PM
Points: 4, Visits: 32

You need to start the string with <HTML> and end it with </HTML>


Thanks for the reply, Sergiy. I went back and attempted this:

Set @Job = <HTML> 'The following opportunity has been changed to ' + @Stage + ': '
+ ' '+'Subject: '+ @Subject + ' ' + 'Project Number: '+ @ProjectNumber
+ ' ' + 'Estimated GP: '+ @GP
</HTML>

but I received an error when I tried to run it. Is this the correct string to place the html code around? If not, where should they go?

Thanks,
John

Post #938471
Posted Wednesday, June 16, 2010 12:13 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 428, Visits: 956
I think he was using general case, as in, all HTML needs to be between <sometag> (open) and </sometag> (close)

Try this as message text:

This is the first line.
This is the second line.</br>



Post #938473
Posted Wednesday, June 16, 2010 12:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 12,916, Visits: 32,077
Set @Job = '<HTML>The following opportunity has been changed to ' + @Stage + ': '
+ '<br />'
+'Subject: '+ @Subject
+ '<br />'
+'Project Number: '+ @ProjectNumber
+ '<br />'
+ 'Estimated GP: '+ @GP
+ '<br /> </HTML>'




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #938485
Posted Friday, June 18, 2010 8:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 3:18 PM
Points: 4, Visits: 32
Thanks for the follow-up, Lowell. Sadly, I am now receiving this email message from the trigger:

<HTML> The following opportunity has been changed to Commit - Verbal Agreement: <br />Subject: ACO: Setup Fee <br />Project Number: 29271<br />Estimated GP: 825.00<br /> </HTML>



It seems to me that I need to use xp_smtp_sendmail if I want to send a message in HTML.

I attempted to use two line breaks in my message, like this:
Set @Job = 'The following opportunity has been changed to '+ @Stage+': '
+ char(13)+ char(10)+char(13)+ char(10)+'Subject: '+ @Subject
+ char(13) + char(10)+char(13)+ char(10)+'Project Number: '+@ProjectNumber
+char(13)+char(10)+char(13)+ char(10)+ 'Estimated GP: '+@GP

It seems to be working, I now just receive a message that is double-spaced. For my purposes that is better than on one line, so I'll take it. However, I'm still interested in knowing why the line break doesn't work with longer strings. Any ideas on this?
Post #939624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse