xp_sendmail - empty subject

Question

xp_sendmail - empty subject

Viewing 15 posts - 1 through 15 (of 28 total)

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

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 1

If your @subject value is set via a process(ie from a table, view, function, or sp)..make sure the user has privs on this process.

HTH

Mathew J Kulangara
sqladventures.blogspot.com

Rupert Woodman SSC Enthusiast Points: 195 More actions · Answer 2

Hi Mathew,

Thanks for the reply.

The SQL I'm executing is exactly as above - literal text, so I don't see that it can be a permission issue.

Rgds

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 3

I think (not sure) you should have sql agent and outlook to be configured with same user.

Kindest Regards,

Amit Lohia

Rupert Woodman SSC Enthusiast Points: 195 More actions · Answer 4

Sorry, I can see I wasn't explict. I am logged in as corpdom\xtest and the services are configured to start under user corpdom\xtest.

rgds

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 5

Can you try with single quote around the subject line. I was just throwing point in dark. I never had this issue and I do not have a server with sp2.

Kindest Regards,

Amit Lohia

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 6

Rupert ..if you can't get xp_sendmail to run...try using xp_smtpmail . You can get it at http:///www.sqldev.net . I find it to be far more reliable than xp_sendmail.

One more question..do you have DBO privs with your user on the database? One thing I've noticed is that this type of thing is usually associated with some kind of privs issue. Even if the step fails sometimes, an email will still be sent.

If you trap the returned status after the step you can tell if the step actually failed.

HTH

Mathew J Kulangara
sqladventures.blogspot.com

Balmukund SSCertifiable Points: 6648 More actions · Answer 7

I saw this behaviour earlier, I changed the outlook default mail editor to Notepad and problem went off.

Please try changing outlook default mail editor and let me know the results.

Rupert Woodman SSC Enthusiast Points: 195 More actions · Answer 8

I appreciate any thoughts you may have Amit.

I've tried both single and double quoutes, to no avail.

Note: exactly the same command was used for both users, so I don't think it's a quoting issue.

Rgds

Rupert Woodman SSC Enthusiast Points: 195 More actions · Answer 9

Hi Mathew,

I'll certainly look at xp_smtpmail. I've not seen the website you mention either, so I'll have a peruse around there at some later point.

I've just spent a couple of minutes checking the permissions and I believe they're the same:

login corpdom\xtest has a role of sysadmin, and database access to XTest

This is the same as corpdom\rupertw

Database user corpdom\xtest has database role membership of public and db_owner

This is the same as corpdom\rupertw

Immediately after the xp_sendmail statement, @@error is 0, which makes me think it thinks it worked correctly.

Thanks for your suggestions. I don't really want to resort to an extra program because it'll mean more to remember when it comes to deployment, but I may have to resort to it.

Rgds

Rupert Woodman SSC Enthusiast Points: 195 More actions · Answer 10

Hi Balmukund,

In Outlook 2003, I can see an option to change to use Word as the default editor, but as I don't have Word installed on the machine, this option is greyed out.

I don't see how I can change to use notepad as the default mail editor - could you elucidate please?

Many thanks

bledu SSChampion Points: 12335 More actions · Answer 11

DECLARE @To VARCHAR(500)

DECLARE @MessageString VARCHAR(500)

DECLARE @User1Email VARCHAR(500)

DECLARE @Topic VARCHAR(200)

SET @MessageString ='This script will send an email'

SET @User1Email = 'someone@mars.com' -- use semi colon to seperate many email addresses

SET @To = @User1Email

SET @Topic = 'Test SQL Send Email'

EXEC Master..xp_sendmail

@recipients = @To

,@message = @MessageString

,@subject = @Topic

Everything you can imagine is real.

Daniel Cho SSC Enthusiast Points: 100 More actions · Answer 12

Can you try turning off caching on your outlook client?

Rupert Woodman SSC Enthusiast Points: 195 More actions · Answer 13

Hi Bledu,

Thanks for your suggestion.

I don't think you understood my problem fully.

I can send an email, and for one user it works perfectly, another user, on the same machine, it sends an email with an empty subject line.

I did a cut & paste of your code, just changing the email address, and it behaved as my current script - the subject line comes through as blank.

Rgds

bledu SSChampion Points: 12335 More actions · Answer 14

I have win xp with latest update, outlook 2003 and sql 2000 with service pack 4.

i tried to setup with similar stuff like yours and it works fine.

this removes the problem of you having to go to an earlier version of outlook.

i can not duplicate your problem though. your settings work fine on my machine.

why don't you try installing service pack 4 for sql server 2000, it might solve your problem.

Everything you can imagine is real.

Rupert Woodman SSC Enthusiast Points: 195 More actions · Answer 15

Thanks for doing all that Bledu - much appreciated.

I'm coming to the conclusion that I'll have to apply a service pack and see what happens. I didn't really want to as it'll then mean I'll have to go through the acceptance process with the users before I can update the live server, which will take time etc, but I guess I have no real choice.

Rgds