Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » xp_sendmail - empty subject
Add to briefcase
28 posts, Page 1 of 3
123»»»

xp_sendmail - empty subject Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Rupert Woodman
Posted Tuesday, February 14, 2006 10:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 14, 2006 4:13 AM
Points: 11, 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
Post #258305
Mathew J Kulangara
Posted Tuesday, February 14, 2006 10:23 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 10, 2008 3:53 PM
Points: 138, 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
Post #258308
Rupert Woodman
Posted Tuesday, February 14, 2006 10:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 14, 2006 4:13 AM
Points: 11, 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
Post #258313
Amit Lohia
Posted Tuesday, February 14, 2006 11:39 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, January 22, 2011 12:01 PM
Points: 702, Visits: 174

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

Kindest Regards,

Amit Lohia
Post #258334
Rupert Woodman
Posted Tuesday, February 14, 2006 11:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 14, 2006 4:13 AM
Points: 11, 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
Post #258335
Amit Lohia
Posted Tuesday, February 14, 2006 11:46 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, January 22, 2011 12:01 PM
Points: 702, 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
Post #258336
Mathew J Kulangara
Posted Tuesday, February 14, 2006 3:06 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 10, 2008 3:53 PM
Points: 138, 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
Post #258410
Balmukund Lakhani-269523
Posted Wednesday, February 15, 2006 12:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 01, 2013 3:41 AM
Points: 339, Visits: 240

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.

 
Post #258497
Rupert Woodman
Posted Wednesday, February 15, 2006 1:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 14, 2006 4:13 AM
Points: 11, 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
Post #258514
Rupert Woodman
Posted Wednesday, February 15, 2006 2:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 14, 2006 4:13 AM
Points: 11, 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
Post #258519
« Prev Topic | Next Topic »

Add to briefcase
28 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.