Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 673
  • 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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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