SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


xp_sendmail - empty subject


xp_sendmail - empty subject

Author
Message
Rupert Woodman
Rupert Woodman
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1
Hi,

I am trying to send mail from SQL Server 2000 but when the email is delivered, the subject line is empty.
I have looked through the forums here and found someone with the same problem which they fixed it by reverting to a previous version of Outlook. Unfortunately, I'm not in a position to be able to do that.

I've also entered:
sql server xp_sendmail subject
into Google, and gone through the first 22 pages of results, but to no avail.

Here is what I have done:

I have a machine nwb-xtest-beta, running Windows XP Pro, with all OS updates, and logged in as "xtest".
I am running SQL Server 2000, with SP2.
I have installed Outlook 2003, installed all the updates available to it, configured a profile ("XTest") and sent and received email fine.
I configured the MSSQLSERVER service to be started as user corpdom\xtest, and restarted the service.
I went to Enterprise manager, Support Services, SQL Mail, and selected XTest from the list of profiles.
I then went to Query Analyzer, and ran:

EXEC xp_sendmail 'rupertw', @subject="test subject", @message="test message"

The message was delivered, but with an empty subject line. There was no subject in the SMTP headers (i.e. there was nothing after Subject: ).

I wondered if it was a problem which had been fixed with a later service pack, so I went to my machine - nwb-rupertw (so I could be sure that it was the application of the SP which had fixed it), running the same software, but configured everything as user "rupertw".

Executing the same SQL statement worked, including a good subject line.

I went back to nwb-xtest-beta and reconfigured everything using the "rupertw" id. Again, it all worked, including good subject line.

No errors reported in event viewer.

It would imply that there is a difference in how xp_sendmail sees the different users, tho I've been told that they're the same.

Has anyone got a suggested solution to this problem?

Many thanks

Rgds

Rupert
Mathew J Kulangara
Mathew J Kulangara
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 24

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
Rupert Woodman
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1
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
Amit Lohia
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 174

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




Kindest Regards,

Amit Lohia
Rupert Woodman
Rupert Woodman
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1
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
Amit Lohia
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 174
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
Mathew J Kulangara
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 24
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 Lakhani-269523
Balmukund Lakhani-269523
Say Hey Kid
Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)

Group: General Forum Members
Points: 664 Visits: 367

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
Rupert Woodman
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1
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
Rupert Woodman
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search