SQL Mail Question
-
Hello All,
I have SQL Mail setup on my SQL 2000 server operating through MS Outlook 2000. The mail option I choice to setup was the internet mail (POP3). The mail functionality is working but the problem is that it only works if I leave Outlook open. Once I close Outlook, I am not notified when a job has completed until I log onto the server and open Outlook. I have another server with SQL Mail running on it, but I'm using MAPI instead of POP3.
Is that normal for the POP3 configuration or did I miss something during the installation and setup of SQL Mail.
-
Simple answer: Yes.
The long answer: SQL Mail requires a MAPI compliant server to connect with directly and work. Because you are using POP3 it doesn't support the same authentication methods and therefore you cannot connect without an intermediate client that supports POP3.
We are going through the same rigamarole here. And to make it even worse, we aren't allowed to us the Admin ID to e-mail. Therefore I have to set it up to use a different userid and profile. I also have to fire outlook using the RunAs function. PITA.
Sorry I couldn't give you answer that you like more.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Best thing you can do to save future headaches is to remove Outlook and MAPI from your server.
Get a replacment like xp_smtp_sendmail
http://www.sqldev.net/xp/xpsmtp.htm
--------------------
Colt 45 - the original point and click interface
-
Phil,
How would you use your technique to have a mail message sent if a job failed? It seems to me that your method works fine if you are sending a planned email message, but how to you send an unplanned one?
That is the main drawback I see to your recommendation. Am I missing something?
-
Very easy solution, the last step in the package is called when one of the previous step fails
eg:
step success action fail action step 1 goto next step goto step 3 step 2 end job sucess goto step 3 step 3 end job fail end job fail Step 3 either calls xp_smtp_sendmail directly, or it calls your own custom stored procedure. I take the latter approach and pass through the Job ID as a parameter to the stored procedure. The stored procedure then extracts the exact error message from the sysjobhistory table.
SELECT TOP 1 'Job: ' + Job.[name], Hst.[sql_message_id], Hst.[message] FROM [msdb].dbo.sysjobhistory Hst INNER JOIN [msdb].dbo.sysjobs Job ON Hst.[job_id] = Job.[job_id] WHERE Hst.[job_id] = @uidJobID AND Hst.[run_status] = 0 AND Hst.[step_id] <> 0 ORDER BY Hst.[run_date] DESC, Hst.[run_time] DESC
For alerts, you have a job with a single step to send the email as detailed above and then setup your alerts to execute the job. You use SQL Agent tokens to provide details of the error. See SQL Agent topic at http://www.sqldev.net
--------------------
Colt 45 - the original point and click interface -
That's a pretty slick way to do it. I'll add it to my arsenal of tricks.
Thanks,
-
Phil,
Thanks for the info. I guess I'm going to have to play with this to see if I can figure out what you have going on. I've been working with SQL 2k for several months now and feel somewhat comfortatble except when it comes to code such as this.
I usually pick up various techniques from sites such as this one for specific code. Like I said, I'll play with it.
Thanks again.
Ronnie
-
I use Outlook 2000 sp1, which does not have to be open on the desktop in order to send mail. I know its not the latest version, but it works pretty well.
-
You must have Outlook configured as a MAPI mail client. I also have another server with Outlook running in MAPI mode and it also works without having to be opened.
The problem that I am having is trying to use Outlook as a POP3 client without having to keep Outlook opened in to send notifications.
Thanks,
Ronnie
-
It is configured to use POP3, and sends mail through our Groupwise pop3 gateway.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply