xp_sendmail works but there is no subject

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 26 Mar 2008 18:23 GMT

Greetings,

I have a stored procedure that sends email. It works fine with the
exception it doesn't include the assigned subject, so the subject is blank.
I am running SQL 2000 SP4 on a Windows Server 2003 box. The mail client is
Outlook 2003 with Exchange 2003 being the mail host.

SQL Server starts using a domain account, SQL Server Agent starts using the
same domain account and an Outlook profile is configured for this account on
the SQL box.

Here is my code:
DECLARE @myRecipient NVARCHAR(100)
DECLARE @myMessage NVARCHAR(255)

SET @myRecipient = @rep + '<domain>'
SET @myMessage = @name + '(' + @contactID + ') has unsubscribed from email
marketing.'

EXEC master.dbo.xp_sendmail @recipients = @myRecipient, @message =
@myMessage, @subject = 'Unsubscribe Notice'

As mentioned, the email gets sent fine but the subject line is blank.

I have had no luck finding a solution for this so I'm hoping someone here
can help.

Thanks,

Mike

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 26 Mar 2008 19:18 GMT

I am not an xp_sendmail expert, but there are lightweight alternatives that
don't require outlook, sql mail, profile configuration, etc. and are not
prone to various SQL Mail or Outlook or Exchange bugs. I suggest trying out
xp_smtp_sendmail as an easy alternative (if you send it through Exchange and
not through Exchange (e.g. simple SMTP server), at least this will tell you
if the problem is with xp_sendmail, the mail server, or your mail client).
http://www.aspfaq.com/2403

> Greetings,
>
[quoted text clipped - 30 lines]
>
> Mike

Reply to this Message

Mike - 26 Mar 2008 22:31 GMT

Aaron,

Thanks for taking the time but I was hoping to get a solution to this
particular issue. From what I've read about xp_smtp_sendmail, I would have
to add steps to all of my existing jobs on the SQL box in order to get
notifications when they are complete. Since I have dozens of jobs, I don't
really want to do that. If what I read on the link you gave me is incorrect,
I might try it but it sounds like if you install xp_smtp_sendmail, email
notifications on jobs will not work anymore unless you add a step manually to
send email using xp_smtp_sendmail.

Mike

> I am not an xp_sendmail expert, but there are lightweight alternatives that
> don't require outlook, sql mail, profile configuration, etc. and are not
[quoted text clipped - 38 lines]
> >
> > Mike

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 26 Mar 2008 22:39 GMT

> Thanks for taking the time but I was hoping to get a solution to this
> particular issue. From what I've read about xp_smtp_sendmail, I would
[quoted text clipped - 8 lines]
> to
> send email using xp_smtp_sendmail.

Yes, that is true. But it is really a one-time cost, and personally for me,
I am willing to create extra steps in my jobs for these things (anyway, I
don't want an e-mail every time every single job succeeds... I get enough
e-mail as it is). Plus using xp_smtp_sendmail it is very easy to create a
wrapper stored procedure to easily customize from, to, etc. in each
individual job step, without having to muck with multiple outlook profiles,
and deal with all the MAPI <-> SQL service interaction headaches.

If job management is really important to you, then you should take a look at
www.sqlsentry.net ... I swear by it.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 26 Mar 2008 22:45 GMT

In any case, did you try Google? Like I said before, I abandoned
xp_sendmail years ago, so am not familiar with this particular issue. But
it looks like a common one:

http://www.google.com/search?q=subject+blank+xp_sendmail

http://groups.google.com/groups/search?ie=UTF-8&q=subject+blank+xp_sendmail+grou
p%3Amicrosoft.public.sqlserver.*&qt_s=Search

> Aaron,
>
[quoted text clipped - 64 lines]
>> >
>> > Mike

Reply to this Message