February 14, 2006 at 10:18 am
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
February 14, 2006 at 10:23 am
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
February 14, 2006 at 10:37 am
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
February 14, 2006 at 11:39 am
I think (not sure) you should have sql agent and outlook to be configured with same user.
Amit Lohia
February 14, 2006 at 11:42 am
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
February 14, 2006 at 11:46 am
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.
Amit Lohia
February 14, 2006 at 3:06 pm
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
February 15, 2006 at 12:46 am
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.
February 15, 2006 at 1:56 am
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
February 15, 2006 at 2:10 am
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
February 15, 2006 at 2:16 am
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
February 15, 2006 at 11:28 am
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
February 15, 2006 at 3:30 pm
Can you try turning off caching on your outlook client?
February 16, 2006 at 2:18 am
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
February 16, 2006 at 2:52 am
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.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply