February 19, 2009 at 8:02 am
Hi-
I have a DTS Package that has the following ActiveX script. Occasionally, our exchange mail servers are unavailable and we need the job to run regardless, I put an error handler in the code that I assumed would resolve the issue, but the job failed this morning because of an Exchange server issue - anyone have an idea how to handle this better?
[font="Courier New"]
Function Main()
Dim Cn, strSQL
' =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
' DTSGlobalVariables
' =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
DBServer = DTSGlobalVariables("DBServer").value
MailTo = DTSGlobalVariables("MailTo").value
strSQL = "driver={SQL Server};server=" & DBServer & ";database=myDB;"
Set Cn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.Recordset")
Cn.Open strSQL
Cn.CommandTimeout = 300
strSQL = "master..xp_sendmail '" & mailTo & "', @subject='Job has Started', @message='Server=" & DBServer & VbCrLf & "Package=myPackage'"
'Error handler in the event exchange servers are unavailable
On Error Resume Next
cn.execute strSQL
'If an error occurred due to exchange servers being unavailable, ignore and clear the error.
If Err Then
Err.Clear
Main = DTSTaskExecResult_Success
End Function
End If[/font]
Here is the error message from this morning:
[font="Courier New"]Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft OLE DB Provider for ODBC Drivers
Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]xp_sendmail: failed with mail error 0x80040115
Error on Line 127
(Microsoft OLE DB Provider for ODBC Drivers (80040e14): [Microsoft][ODBC SQL Server Driver][SQL Server]xp_sendmail: failed with mail error 0x80040115)
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4500[/font]
Thanks in advance for any and all advice!
February 26, 2009 at 7:28 am
Instead of using xp_sendmail like this, I create a table with fields for recipient, subject, message body and a nullable date field message_sent. I then have a job running every 15 minutes which looks for records with message_sent IS NULL, and executes xp_sendmail using the data in the table for the parameters, then setting message_sent to getdate(). This way if mail goes down, not only will your jobs continue but you'll keep a permanent record of all the messages that would have been sent.
Scott
--
Scott
February 26, 2009 at 8:48 am
It's been awhile since I've worked with ADO, but I think you might be better off using a commandType of stored procedure and use parameters. Then you can get the return value from xp_sendmail and handle a failed execution. Check out these links:
http://forums.devx.com/showthread.php?t=47397
http://support.microsoft.com/kb/194792
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply