Subject in dbmail

Question

Post reply

Subject in dbmail

sharon_goltz

SSC Enthusiast

Points: 116
More actions
February 23, 2010 at 7:15 am

#138298

Hi,
I'm running a procedure that sends mails to recipients.
It works fine most of the time but i noticed that on occasion it sends "SQL Server Message" in the Subject instead of the subject i specified in the procedure.
The code looks something like this:
declare @From varchar(100)
declare @To varchar(100)
declare @Subject varchar(100)
declare @Body varchar(4000)
-- To owner
select @from =LastUserNameEmail ,
@To= OwnerEmail ,
@Subject= 'Your PR No.' + LTRIM(STR(PurReqId))+ ' for total sum of '+ltrim(str(Total))+'$'+' was forwarded to '+ NextApprovalName + Declined_Ind,
@Body= '<html> <head> <title>Purchase Requisition No </title> </head> <body> '
from dbo.QWF_Approval_Mail_Info
where PurReqId = @ipurreq
--exec dbo.sp_send_cdosysmail @From,@To,@Subject,@Body
exec msdb.dbo.sp_send_dbmail
@profile_name='RiTReports', @recipients=@To, @subject=@Subject, @body=@Body, @body_format='HTML'
What causes this problem? I really don't have any direction at all...
Thanks!
Sharon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Cory E. SSCertifiable Points: 7076 More actions · Answer 1

Per BOL:

[ @subject = ] 'subject'

Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is 'SQL Server Message'.

I bet that "@Subject= 'Your PR No.' + LTRIM(STR(PurReqId))+ ' for total sum of '+ltrim(str(Total))+'$'+' was forwarded to '+ NextApprovalName + Declined_Ind, " one of the values is NULL, which will set the whole variable null.

-- Cory

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

Good catch.. CONCAT NULL YIELDS NULL in play.. I'd bet you are right..

CEWII

sharon_goltz SSC Enthusiast Points: 116 More actions · Answer 3

Thanks for the quick replies.

I guess you are right about the NULL and concatenating it although i can't figure out why it's happening.

The question now is how do i avoid sending this message with null value?

Thanks again!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

You can use the ISNULL( fieldname, '' ) function around the items that are used to assembly the string. Keep in mind that sometimes things end up NULL when we least expect them so try not to have too expectations that a NULL can't happen at any particular place. You can also set the CONCAT null option of to off for the sproc but I don't generally like to do such things when it is far better to resolve the issue in the first place.

BTW that is two single quotes in the ISNULL example.

CEWII