Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

xp_sendmail - empty subject Expand / Collapse
Author
Message
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
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
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
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
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
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
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
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: Wednesday, March 19, 2014 12:27 AM
Points: 344, Visits: 262

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
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
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 123»»»

Permissions Expand / Collapse