SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Carriage return in xp_sendmail
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnnyryall8
Starting Member

USA
1 Posts

Posted - 06/01/2010 :  15:52:39  Show Profile  Reply with Quote
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 the subject is more than 32 characters the carriage return/ line break characters don't seem to work.

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

I'm using SQL Server 2000 and here is the code 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


Any suggestions are appreciated!

Thanks,

John
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000