Post reply

Carriage return in xp_sendmail

  • June 3, 2010 at 10:25 am

    #74066

    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

  • June 3, 2010 at 10:51 am

    #1176084

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 3, 2010 at 11:03 am

    #1176088

    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.

  • June 3, 2010 at 12:09 pm

    #1176114

    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

  • June 15, 2010 at 6:43 am

    #1180351

    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>

    _____________
    Code for TallyGenerator

  • June 16, 2010 at 6:27 am

    #1180907

    Thanks Sergiy!

    It works (at least with xp_smtp_sendmail)!!!

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

  • June 16, 2010 at 12:10 pm

    #1181216

    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

  • June 16, 2010 at 12:13 pm

    #1181218

    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>

  • June 16, 2010 at 12:44 pm

    #1181229

    Set @Job = '<HTML>The following opportunity has been changed to ' + @Stage + ': '

    + '<br />'

    +'Subject: '+ @Subject

    + '<br />'

    +'Project Number: '+ @ProjectNumber

    + '<br />'

    + 'Estimated GP: '+ @GP

    + '<br /> </HTML&gt'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 18, 2010 at 8:11 am

    #1182150

    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?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply