Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

xp_sendmail not sending @message text Expand / Collapse
Author
Message
Posted Monday, March 11, 2013 9:12 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:12 PM
Points: 55, Visits: 378
I just figured out what my issue was. And as usual, it was something I was doing wrong. Albeit it's odd it works this way.

I was assigning several values to a @BodyText variable. Building a body message really. Then sending that @BodyText to the @Body for the sql server email.

The kicker here is that if any of the values are null, or blank (??), it nulls out the whole @BodyText variable & sends nothing at all. You'd think, logically, that if a value was blank or null, it would put that in that section & just send a blank value.

Here's my example:

Set @BodyText = 'Mirroring Status Alert!!!' + CHAR(13) + CHAR(13) +
'Date: ' + @DateTime + CHAR(13) +
'Server: ' + @@ServerName + CHAR(13) +
'Database: ' + UPPER(@DatabaseName) + CHAR(13) +
'Mirror Status: ' + CAST(@MirrorStatus AS VARCHAR(1)) + ' - ' + @MirrorStatusDescription + CHAR(13) + 'Unsent Log size: ' + @UnsentLogStatus

So if @MirrorStatusDescription or @UnsentLogStatus somehow ended up blank, the whole @BodyText was blank. That's just plain silly.

Anyways...I fixed it. I'm not sure why others were experiencing it unfortunately.



======================================================
John
SQL Server 200x Newb and proud of it!
======================================================
Post #1429332
Posted Monday, March 11, 2013 9:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:29 AM
Points: 226, Visits: 344
To avoid this you can use:
SET CONCAT_NULL_YIELDS_NULL OFF

This will prevent a field where the value is NULL from making the entire result NULL as well.

Post #1429337
Posted Monday, March 11, 2013 9:24 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:12 PM
Points: 55, Visits: 378
zulmanclock (3/11/2013)
To avoid this you can use:
SET CONCAT_NULL_YIELDS_NULL OFF

This will prevent a field where the value is NULL from making the entire result NULL as well.



Ahhhh....That did the trick!! Thank you, very much!!!



======================================================
John
SQL Server 200x Newb and proud of it!
======================================================
Post #1429344
Posted Monday, March 11, 2013 9:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:29 AM
Points: 226, Visits: 344
No problem at all.
Post #1429346
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse