xp_sendmail not sending @message text

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » xp_sendmail not sending @message text

14 posts, Page 2 of 2

««1 2

xp_sendmail not sending @message text

Rate Topic

Display Mode

Topic Options

Author

Message

John Waclawski

Posted Monday, March 11, 2013 9:12 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:16 AM
Points: 55, Visits: 316

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! BigGrin

======================================================

Post #1429332

zulmanclock

Posted Monday, March 11, 2013 9:17 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:42 AM
Points: 207, Visits: 217

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

John Waclawski

Posted Monday, March 11, 2013 9:24 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:16 AM
Points: 55, Visits: 316

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! BigGrin

======================================================

Post #1429344

zulmanclock

Posted Monday, March 11, 2013 9:25 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:42 AM
Points: 207, Visits: 217

No problem at all.

Post #1429346

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions