asked on

xp_smtp_sendmail

I have problem sending email using xp_smtp_sendmail with SQL2000. When message body exceeds certain length, email is sent with blank message body. On documentation states that @message parameter can have up to 8000 characters

Any solution?

Thank you in advance

SET @leave_details_text=
 
	+'<br>Leave ID:'+CAST(@leave_id as VARCHAR)
	--+'<br>Staff ID:'+CAST(@pers_code as VARCHAR)
	+'<br>Type of leave:'+@leave_name 
	+'<br>Number of days:'+cast(@nr_days as varchar)
	--+'<br>Start Date:'+CONVERT(CHAR(19),@start_date)
	--+'<br>End Date:'+CONVERT(CHAR(19),@end_date)
	--+'<br>Half day at the start:'+@half_day_start 
	--+'<br>Half day at the end:'+@half_day_end
	--+'<br>Note:'+@note
	--+'<br>Date requested:'+CONVERT(CHAR(19),@request_date)
	--+'<br>Initial Plan:'+@initial_plan_date
	--+'<br>Plan date:'+CONVERT(CHAR(19),@plan_date)
	+'<br><br>Backstop staff:'+@backstop_fullname 
	+'<br>Backstop Status:'+@backstop_status 
	+'<br>Backstop approval date:'+CONVERT(CHAR(19),@backstop_approve_date) 
	+'<br><br>Supervisor:'+@supervisor_fullname
	+'<br>Supervisor approval date:'+CONVERT(CHAR(19),@superviser_approva_date)
	+'<br>Status:'+@supervisor_status 
	--+'<br><br>Last edit:'+CONVERT(CHAR(19),@last_edit)
 
 
 
SET @mail_subject=@pers_fullname+' '+@pers_email
IF @status_code=0   -- Submited leave 
 
	BEGIN
	SET @FROM=N'LeaveMonitor@xxxxx.org'
        SET @FROM_NAME=N'Leave Monitor' 
        SET @TO=N''+@backstop_email  
        SET @CC=N''+@pers_email
        SET @BCC=N'LeaveMonitor@xxxxx.org'
        SET @subject=N'Request for backstop from ' +@pers_fullname
 
        SET @message=N'<HTML>Dear '+@backstop_fullname
	+@pers_fullname+' requested backstop for period '
	+CAST(@start_date as VARCHAR)+' until '+CAST(@end_date as VARCHAR)+'<br><br>'
	+'Please access Leave Monitor to approve or reject request. <p><p>'
	+'<font size="2"<u>Leave Record Details</u>'
 
	SET @message=@message+@leave_details_text;

Open in new window

ASKER CERTIFIED SOLUTION

BernFarrant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Raja Jegan R

Try RTRIM() of all Varchar columns you use while selecting @message and @leave_details_text for the email message and check again. Check the length whether it exceeds on any of the columns.

belallari

ASKER

Yes you are right. There are NULL variables on the string.
I added SET CONCAT_NULL_YIELDS_NULL OFF on the code and everything works fine.

http://msdn.microsoft.com/en-us/library/ms176056.aspx

Thank you very much