Posted on 2009-02-11
Last Modified: 2012-06-27
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_NAME=N'Leave Monitor' 

        SET @TO=N''+@backstop_email  

        SET @CC=N''+@pers_email

        SET @BCC=N''

        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

Question by:belallari
    LVL 1

    Accepted 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.
    LVL 57

    Expert Comment

    by: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.

    Author Closing Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now