POP3/SMTP mail support requires Outlook Client to be Active
-
I'm wondering if there are any work arounds to having the Outlook 2002 or Outlook 2003 client actively running in order for SQL Mail to work using POP3 and SMTP? Is there any real issues in just using the Outlook 2000 client that isn't required to be actively running in order to make SQL Mail work using POP3 and SMTP.
Background information in the KB article:
http://support.microsoft.com/kb/263556/en-us
Gregory A. Larsen, MVP
-
This was removed by the editor as SPAM
-
I believe there is a security issue according to an article that I read.
I looked into POP3/SMTP with a no. of configurations, MS Exchange, Lotus Notes mail servers with different mail clients etc. at different orgs that I have worked and each time I have come to the conclusion that it's more trouble than its worth to use SQL Mail.
I've always gone for CDO objects and never had a problem.
Paul R Williams.
-
Paul, thank you for the information. I would like to go with CDO exclusively but can't because I will lose the SQL AGENT mail notification. And I would like to have the notification services of SQL AGENT so I can get notified when jobs fail for one thing. So I've decided to use the Outlook 2000 client at this point, even if it has security issue. But I have decided to create a mail POP3/SMTP mail profile to support only SQL Agent mail. Because SQL Agent mail only requires SMTP, I can have a bogus POP3 definition and everything works fine. Plus our security staff said no on getting a POP3 service and account. Now this mail profile with a bogus POP3 definition will not work for the SQL MAIL that is run under the MSSSQLSERVER service, since MSSQLSERVER needs to read mail before it will send mail. So allow mail to be send through MSSQLSERVER service I've decided to use CDO. This works fine, since in our environment we don't need to have SQL Server reading any mail.
Gregory A. Larsen, MVP
-
Greg,
I understand about the SQL Agent mail notification with CDO, but do you need to use this ?, or have I misinterpreted what you've stated above.
I use CDO even for job failures. Just add an extra step in the jobs which runs whenever any other step fails. This step is just T-SQL that calls the CDO object, so you don't actually need SQL Agent mail notification itself.
Paul.
Paul R Williams.
-
Yes I want to get SQL Agent Notification via email as it was designed. I don't want to use a kludge of a way of getting notified.
Gregory A. Larsen, MVP
-
Paul,
you could also add a trigger to the sysjobhistory table to email when a job fails. This negates you having to micromanage the job failures notifications.
I do this and include the failed data from the job.
-
Now there is a great way to get notified. Beats adding code to every job.
Gregory A. Larsen, MVP
-
The problem is that with over 135 jobs it's a nightmare logistically.
-
Wish I could take the credit for the idea, but someone else in the forums gave it to me....here's the code that I use... I am using a proc usp_sendmail which calls the xp_smtp dll, and already has the configuration information with regards gateway ip...
/* drop trigger trg_stepfailures */
CREATE trigger trg_stepfailures
on sysjobhistory
for insert
as
declare @strcmd varchar(1000),@strRecipient varchar(500),@strMsg varchar(3000), @subject varchar(100)
set @strRecipient = 'email@address.com'
if exists (select * from inserted where run_status = 0 and step_name <> '(job outcome)')
begin
select @strMsg = @@servername + ' Job ' + sysjobs.name +char(13) + char(13) + 'Step ' +
inserted.step_name +char(13)+char(13)+ 'Message ' + inserted.message
from inserted
join sysjobs
on inserted.job_id = sysjobs.job_id
where inserted.run_status = 0
select @subject = 'Job ' + sysjobs.name + ' Failed on Server' from inserted join sysjobs on inserted.job_id = sysjobs.job_id where inserted.run_status = 0
-- raiserror (@strMsg, 16, 10) with log
exec master.dbo.usp_sendmail @recipients = @strRecipient,
@msgtext = @strMsg,
@mailsubject = @subject
end
-
?? logistical nightmare to add the steps.
This runs through all jobs on the server. You could modify it a bit to exclude jobs and maybe put it in a proc that is run on a regular basis.
DECLARE @JobID uniqueidentifier
DECLARE @JobName sysname
DECLARE @step_id int
DECLARE @jobs TABLE (job_id uniqueidentifier, job_name sysname)
-- Add step if missing, and not excluded
INSERT @jobs (job_id, job_name)
SELECT j.Job_id, j.[name]
FROM msdb.dbo.sysjobs j
LEFT JOIN (
SELECT job_id
FROM msdb.dbo.sysjobsteps
WHERE step_name = 'ReportFailure'
) as Stp
ON j.job_id = stp.job_id
WHERE stp.job_id IS NULL
-- loop through temp table adding jobsteps
WHILE EXISTS(SELECT 1 FROM @jobs)
BEGIN
SELECT TOP 1 @JobID = job_id, @JobName = job_name
FROM @jobs
ORDER BY job_name
-- call sp_add_jobstep to add step. Omitting @step_id
-- parameter means that step is added to the end
EXEC msdb.dbo.sp_add_jobstep
@job_id = @JobID
, @step_name = 'ReportFailure'
, @on_success_action = 2 -- failure
, @on_fail_action = 2 -- failure
, @subsystem = 'TSQL'
, @database_name = 'DBA'
, @command = 'EXEC dbo.usp_ReportFailure [JOBID]'
-- get the number of the newly created step
SELECT @step_id = step_id
FROM msdb.dbo.sysjobsteps
WHERE step_name = 'ReportFailure'
AND job_id = @JobID
-- update on failure action for all prior steps
UPDATE msdb.dbo.sysjobsteps
SET on_fail_action = 4 -- goto step
, on_fail_step_id = @step_id
WHERE step_id < @step_id
AND job_id = @JobID
DELETE @jobs WHERE job_id = @JobID
END
--------------------
Colt 45 - the original point and click interface
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply