"Send Mail Task" completing but where''s my e-mail?!
-
Hi all,
For background : I have a DTS package and I'm trying to e-mail an Excel spreadsheet using a Send Mail Task in a DTS design sheet. It's saying it's completing but no e-mail is arriving. Ignore this for a mo and allow me to describe the problem in its most basic form:
- I create a DTS package and add a Send Mail Task.
- In the task, I choose the default (actually, only) profile, pop in a password (have tried without), add an internal and external address, a subject and a message, and no attachment.
- If I run the package it says that it's complete, but the e-mail does not arrive at any address.
I can send mail with all the EM test buttons (e.g. in the server props, the Agent props, a maintenance plan props, under reporting). I can send mail using xp_sendmail. All of this with the same MAPI profile and to the same addresses. I can verify this by opening Outlook on the machine and seeing the e-mails in the Sent folder.
My setup is:
- Windows Server 2003
- SQL Server 2000 SP4
- Outlook 2003
- Exchange 2003 (on another server)
I've tried to track this in Exchange and I can't find any trace of the supposedly sent DTS messages. I'm not sure where to look for this in which logs, and whether their would be any information. It seems odd that the task is completing with no errors and yet the e-mail is not being sent.
This has been my first foray into the world of SQL Mail, so I'm wondering if I've missed something.
Any ideas, or any alternatives?
Thanks,
Ali
-
Hello Ali,
You can create a log file for the DTS package and then execute the package, so that it logs all the information and you can track of the relevant error.
Right mouse click on the DTS designer window, select package properties, logging tab, Error handling (give the necessary file)
Hope this helps.
Thanks
Lucky -
Hi Lucky,
Many thanks for the reply - I will run through that file.
As an aside, I created an Execute SQL task in the same test package and called xp_sendmail. This worked, but the Send Mail Task in the same package still completed but never arrived! I may just go down the xp_sendmail route, but I'd like to know why it's not working (particularly if I want to work with the Send Mail task object in the future), so I'm gonna have a rifle through that log file.
Thanks again,
Ali
-
Right, I've looked at the error log file and there's no indication that the SendMailTask failed:
Step 'DTSStep_DTSSendMailTask_1' succeeded Step Execution Started: 21/12/2006 09:34:22 Step Execution Completed: 21/12/2006 09:34:23 Total Step Execution Time: 0.532 seconds Progress count in Step: 0
So in terms of understanding why I can't find the e-mails sent by Send Mail, I'm unfortunately back to the beginning.
I've had a look in message tracking in Exchange, and can't find any of these, so I don't believe they're even getting beyond the MAPI client.
Ali
-
I don't know if this helps but when I did this on our server, the mail did not send unless the Outlook client on the server was opened. Opening the Outlook application caused all the emails created by the dts package to be sent.
-
That was it! I opened Outlook and all the DTS "Send Mail Task" attempts I'd been trying suddenly came through. Thank you, both lucky and loobyloo for the replies!
I've got to say, keeping Outlook open on a server purely for the sake of running a DTS package which may be scheduled is a pretty ugly situation, especially as all other MAPI interfaces in SQL seem to behave properly. At least through this, I've been introduced to xp_sendmail, and got an alternative to keeping Outlook open!
Thanks again,
Ali
-
Maybe it would be better to replace the Send Mail Task with a TransactSQL Task and use XP_Sendmail.
Merry Christmas! ß Click Here
-
Even better would be to use this:
http://www.sqldev.net/xp/xpsmtp.htm
We have switched over to this due to continual headaches with xp_sendmail and Outlook. Any DTS task that needs to send email now uses a SQL Task and executes master.dbo.xp_smtp_sendmail.
-
Thanks for the link - I will look that up.
One more request for advice - I am looking to set up e-mail for another SQL Server (same set up as above, but I might need to create a POP3 mail account, rather than Exchange).
It seems from what I've been reading that MAPI / SQL Mail is problematic (I'd seen some other articles on people going down the SMTP route), but it looks like a lot of the features in Enterprise Manager inherently need to use SQL Mail. I'm talking about the alerts for maintenance plans and e-mailing job logs etc.
How have people approached this, when they don't want to use a MAPI profile? Say I create a maintenance plan and use the reporting tab to notify me of the job logs on completion - I need to have SQL Mail workign for this to happen, don't I? I realise that I could create a T-SQL call that uses xp_smtp_sendmail, or even call CDONTS, and schedule that in after the maintenance plan completes. I would be interested in what other people have done if they've avoided the MAPI.
To be honest I'm not keen on setting Outlook up on each of our DB servers, but equally I don't want to abandon the EM interface for notifications as any workaround automatically makes me more of a single point of support in our organisation! I realise I probably can't have one and the other.
Thanks in advance,
Ali
-
>>I need to have SQL Mail workign for this to happen, don't I?
Instead of relying on setting up the "Notification" tab, we use the "Advanced" tab under job steps.
For the main job task, we set an On Failure step, and the failure step is another T-SQL task that calls xp_smtp_sendmail. Bit more work in initial setup, but at least you get the emails reliably.
-
Thanks for the reply - the information is so useful. We have only recently been able to start tightening up our notifications from SQL servers and it is really helpful to find out what actually works for folks!
Ali.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply