October 24, 2003 at 7:57 am
Hi,
We need to be able to send mail from a server which has no MAPI client. We have found a shareware extended stored procedure called xpsmptd.dll [sqldev.net/xp/xpsmtp.htm] and would like to hear from anyone who has used it (successfully or not).
Thanks
October 24, 2003 at 9:17 am
Sorry folks, one more thing.
Is there any chance that I can use this extended stored procedure for notifying scheduled job failures?
Thanks
October 24, 2003 at 10:50 am
Haven't used that one, but I have used ASPEmail (http://www.aspemail.com).
To call this you need to use sp_OACreate (see http://support.persits.com/show.asp?code=PS01050851 for details)
You would need to add extra steps into the jobs which run on failure to send emails.
Steven
October 26, 2003 at 6:19 pm
xpsmptd.dll works great (keeps you from having to install Outlook or the like). Once you have copied the DLL and run the XP, simply add a step to your job that calls it. An example would be...
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM= N'superman@govconnect.com',
@FROM_NAME= N'MYCOMPANY Data Center',
@TO= N'superman@mycompany.com,batman@mycompany.com',
@subject= N'SCC Daily Data Move and Update Failed',
@type= N'text',
@server = N'mail.mycompany.com'
select RC = @rc
October 26, 2003 at 6:22 pm
You can write your own Active-X DLL using the CDO library. This is incredibly easy using ,NET (just make sure it is compiled for interoperability). You then call is with spao_create. Make sure to include the reference:
Imports SM = System.Web.Mail
if you want it formatted as HTML (which the example below supposes)
sample .NET code:
Public Function SendHTMLMessage(Optional ByVal ReplaceWhat As String = "", Optional ByVal ReplaceWithWhat As String = "") As String
Dim body As String
If mailRecipient.Length = 0 Then
Return "ERROR: No recipient"
Exit Function
End If
If mailMessageFile.Length = 0 Then
Return "ERROR: No message"
Exit Function
End If
Dim msg As New SM.MailMessage
Try
body = readHTMLMailBodyFile(mailMessageFile)
Catch ex As Exception
Return "ERROR: " + ex.Message + " | " + ex.Source
Exit Function
End Try
If ReplaceWhat.Length > 0 Then
body = body.Replace(ReplaceWhat, ReplaceWithWhat)
End If
If body.IndexOf("ERROR") > 0 Then
Return body
Exit Function
End If
With msg
.BodyFormat = SM.MailFormat.Html
.Body = body
.To = mailRecipient
.Subject = mailSubject
.From = mailFrom
.Cc = mailCC
.Bcc = mailbCC
End With
Dim mServer As SM.SmtpMail
mServer.SmtpServer = mailServer
Try
mServer.Send(msg)
Catch ex As Exception
Return "ERROR: " + ex.Message + " | " + ex.Source
Exit Function
End Try
msg = Nothing
mServer = Nothing
Return ("sent to " + mailRecipient)
End Function
October 27, 2003 at 1:59 am
Thanks everyone. I think we will stick with xp_smtp for the moment, given that the e-mails will be triggered from scheduled jobs.
An alternative I thought of for notifying failures is to trigger an alert on a job failure and then make the alert call the SP that generates the e-mail. In that way my job script does not need to contain all the e-mail notification settings.
Mauro
October 27, 2003 at 12:34 pm
I am using xpsmptd.dll, works great. The only observation is that after 1000 chars in the message is inserting a space so if you intend to send HTML formated messages be aware of that.
Ionel
October 28, 2003 at 9:47 am
I am using xp_SMTP_Sendmail. It works great.
The following is code that creates a job that check through every other production job and emails you if one of them failed. You need to change the email address and supply the SMTP server IP address as per the xp_SMTP_SENDMAIL documentation.
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Check for Failed jobs')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Check for Failed jobs'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Check for Failed jobs'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Check for Failed jobs', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Check Jobs', @command = N'
DECLARE @FailedJobs TABLE (
JobName sysname NOT NULL ,
JobDescription nvarchar(512) NOT NULL ,
RunDate int NOT NULL,
Status varchar(15) NOT NULL)
INSERT INTO @FailedJobs
SELECT DISTINCT j.name AS "Job Name"
, j.description AS "Job Description"
, h.run_date AS LastStatusDate,
CASE h.run_status
WHEN 0 THEN ''Failed''
--when 1 then ''Successful''
--when 3 then ''Cancelled''
--when 4 then ''In Progress''
END AS JobStatus
FROM sysjobhistory h, sysjobs j
WHERE j.job_id = h.job_id AND h.run_date =
(SELECT MAX(hi.run_date) FROM sysjobhistory hi WHERE h.job_id = hi.job_id)
AND h.run_time =
(SELECT MAX(h2.run_time) FROM sysjobhistory h2
WHERE h.job_id = h2.job_id AND h.run_date = h2.run_date)
and h.run_status = 0 --keep only failed jobs
ORDER BY 1
--SELECT * FROM @FailedJobs
DECLARE @num INT
SELECT @num = COUNT(*) FROM @FailedJobs
IF @num > 0
BEGIN
DECLARE @List varchar(2000)
DECLARE @name varchar(128)
SET@List = ''''
IF @num > 1 -- use cursor to get list
BEGIN
DECLARE c1 CURSOR FORWARD_ONLY
FOR SELECT JobName FROM @FailedJobs
OPEN c1
FETCH NEXT FROM c1 INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @List = @List + @name + '' , ''
FETCH NEXT FROM c1 INTO @name
END
CLOSE c1
DEALLOCATE c1
SET @List = substring(@List, 1, len(@List)-1)
END
ELSE
BEGIN
SET SELECT @List = JobName FROM @FailedJobs
END
--PRINT @List
EXEC master.dbo.xp_smtp_sendmail
@FROM= N''servername@SQL.Server'',
@TO= N''youremail@company.com'',
@cc= N'''',
@BCC= N'''',
@priority= N''HIGH'',
@subject= N''Production Job(s)That Failed'',
@message= @List, --N''Job failed'',
@attachment= N'''',
@attachments= N'''',
@codepage= 0,
@server = N''127.0.0.1''
END', @database_name = N'msdb', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 2, @on_fail_action = 4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Send Failure Report', @command = N'declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM= N''server@SQL.Server'',
@TO = N''youremail@company.com'',
@cc = N'''',
@BCC= N'''',
@priority= N''HIGH'',
@subject= N''Check Failed Maintenance Job'',
@message= N''Job failed'',
@server = N''127.0.0.1''
select RC = @rc
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 2, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Daily Run', @enabled = 1, @freq_type = 4, @active_start_date = 20030925, @active_start_time = 40000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Edited by - fhanlon on 10/28/2003 09:50:18 AM
Francis
October 29, 2003 at 3:10 am
Thanks for that SP fhanlon.
Being devil's advocate, the only thing I would say is: "What if a job has failed because for some reason the SMTP server is down. In that case how can you flag that error condition given that you cannot send e-mails?"
As a general observation, it doesn't seem to me that the whole notification thing in SQL server is flexible enough to cope with machines where you can't use SendMail or machines placed outside a DMZ (where netsend is of no use because the network is not accessible). The only other alternative appears to be to write to the event log, but then I need another way of polling the event log for error conditions.....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply