July 13, 2011 at 10:24 am
xp_sendmail will send out the email with correct to address, correct subject line but the message text is blank. This has been working.
We were just migrated to Exchange 2010.
We have ran xp_stopmail, recreated outlook profile and SQL mail profile, then ran xp_startmail.
We are out of ideas. Any help will be greatly appreciated.
Thanks
Jack
July 13, 2011 at 10:30 am
are you concatenating strings, and one of them is null?
you know the old SET @string = Firstname + ' ' + LastName,
where ofirstname might be null or something?
Lowell
July 13, 2011 at 10:35 am
Running this through query sends email with no message. to line and subj line are fine.
EXEC master.dbo.xp_sendmail @recipients = 'jm7563@att.com',
@message = 'test message',
@subject = 'Peer One Salary Recommendation Alert'
August 18, 2011 at 9:10 am
Just kicking to the top. We still need an answer to this issue.
February 29, 2012 at 11:56 am
I'm running into this too. In my case it's an existing process on a SQL 2000 SP4 box that started erroring sending mail (no error messages that appear useful). I stopped then started the mail session and mail started sending but the message body is always blank even when passing in a normal string. The goal is to email query results in the message body. I'm able to attach them and we're doing that for now as a workaround. I've already tried the following:
http://support.microsoft.com/kb/314304
The registry key that it said to remove doesn't exist and I tried both true and false (and not passing in) @ansi_attachments with no success. It's a production box so I'm working on arranging a reboot but I'm not holding much hope for that. Does anyone have any thoughts?
March 1, 2012 at 1:04 pm
We believe the issue is how MS SQL 2000's SQL Mail interacts with the newest version of MS Exchange Server (our problem started when the Exchange server was updated).
I found an addin function, xp_smtp_sendmail, that fixes the problem.
Go to: http://www.sqlservercentral.com/Forums/FindPost320477.aspx
March 2, 2012 at 7:35 am
That's probably what did it for us. I found out that they did some mailbox moves this week and the account is currently on the new server. The tech that did the moves is out so I haven't been able to confirm that this mailbox moved but it seems to be the most likely case.
As a note, the link to the package in the other post is broken. We have a copy I can use from another server though.
January 7, 2013 at 9:51 am
I am having this same issue. I use the same email account on 2 SQL 2000 servers. Everything works great on one of hte servers, but the @message is not being sent on the other server. I have verified that everything is set up the same on both servers. Any ideas?
March 11, 2013 at 8:46 am
I too am running into this with SQL Server 2008 R2 at the moment.
I'm getting a subject line, no body text. Still plugging away at it. If I get it running, I'll post what I did to get it running.
Until then, if anyone has a fix, please elaborate.
Thanks!
March 11, 2013 at 9:02 am
John Waclawski (3/11/2013)
I too am running into this with SQL Server 2008 R2 at the moment.I'm getting a subject line, no body text. Still plugging away at it. If I get it running, I'll post what I did to get it running.
Until then, if anyone has a fix, please elaborate.
Thanks!
Here is a piece of code i use for sending mail in SQL Server 2008 R2:
EXEC msdb.dbo.SP_SEND_DBMAIL @PROFILE_NAME = 'Your Profile Here',
@Recipients ='test@email.com',
@Subject = 'Subject Line',
@body = 'Your Message Here'
This article gives you step by step to setup db mail if you have not done so already:
March 11, 2013 at 9:12 am
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.
March 11, 2013 at 9:17 am
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.
March 11, 2013 at 9:24 am
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!!!
March 11, 2013 at 9:25 am
No problem at all.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply