• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:


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>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>Supervisor approval date:'+CONVERT(CHAR(19),@superviser_approva_date)
	--+'<br><br>Last edit:'+CONVERT(CHAR(19),@last_edit)
SET @mail_subject=@pers_fullname+' '+@pers_email
IF @status_code=0   -- Submited leave 
	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

1 Solution
Have you verified that none of the variables you are using are NULL? During string concatenation if one variable is NULL, then the whole string will be NULL.

DECLARE @somevar VARCHAR(50)
SET @somevar = NULL;
SET @message = 'test' + @somevar;

The result of the above will not be a value of 'test' in @message, but rather NULL.
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
belallariAuthor Commented:
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.


Thank you very much
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now