DTS behavior differs, but succeeds, when scheduled

[tbellomo]

OK...so I was having problems scheduling my DTS's, but those are now resolved. They run on schedule, and succeed.

The funny thing is that one portion of the code doesn't run.

I use this ActiveX in the DTS to open a database, and run its macro:

Function Main()

Dim objDB
 Set objDB = CreateObject("Access.Application")
 objDB.OpenCurrentDatabase(DTSGlobalVariables("strDBPath"))
 objDB.DoCmd.RunMacro "mcrOpenGateway"
 objDB.DoCmd.RunMacro DTSGlobalVariables("strMacroName")
 objDB.CloseCurrentDatabase
 objDB.Quit

	Main = DTSTaskExecResult_Success
End Function

One of the macro steps works when "executed" but is apparently omitted when scheduled. It's a "RunCode" that calls this function to send a LotusNote:

'Declare public object variables
Public mkfDoc As String
Public Subject, Attachment, Recipient, copyto, BodyText, UserName, SaveIt

Public Maildb As Object        'The mail database
Public MailDbName As String    'The current users notes mail database name
Public MailDoc As Object       'The mail document itself
Public AttachME As Object      'The attachment richtextfile object
Public Session As Object       'The notes session
Public EmbedObj As Object      'The embedded object (Attachment)
Global MailTo() As String
Global SendSuccess As Boolean

Public Function sendNotesDist(txtSubject As String, txtBody As String, txtAttachPath As String, ReportLogID)
On Error GoTo sendNotes_Err

'Build distribution list
    Dim txtDistList()
    Dim db As Database
    Dim rs As Recordset
    Dim SQL As String
    Dim i As Integer
    
    SQL = "Select LotusNotesName From tblDistribution Where ReportLogID=" & ReportLogID
    
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset(SQL)
    
    If rs.BOF And rs.EOF Then Exit Function
    
    rs.MoveLast
    rs.MoveFirst
    
    ReDim txtDistList(rs.RecordCount)
    
    i = 0
    Do Until rs.EOF
    txtDistList(i) = rs(0)
    rs.MoveNext
    i = i + 1
    Loop
    

'Set up the objects required for Automation into lotus notes
    Subject = txtSubject
    Attachment = txtAttachPath
    Recipient = txtDistList()
'Set bodytext for mail offer - language depends on field in offprofrm
    BodyText = txtBody
'Start a session to notes
        Set Session = CreateObject("Notes.NotesSession")
'Open the mail database in notes
        Set Maildb = Session.GETDATABASE("", MailDbName)
        If Maildb.ISOPEN = True Then
            'Already open for mail
        Else
            Maildb.OPENMAIL
        End If
'Set up the new mail document
        Set MailDoc = Maildb.CREATEDOCUMENT
        MailDoc.Form = "Memo"
        MailDoc.sendto = Recipient
        MailDoc.Subject = Subject
        MailDoc.body = BodyText
        MailDoc.SAVEMESSAGEONSEND = True
'Set up the embedded object and attachment and attach it
        If Attachment <> "" Then
            Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
            Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
            MailDoc.CREATERICHTEXTITEM ("Attachment")
        End If
'Send the document + notify
        MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
        MailDoc.Send 0, Recipient
        Dim msgBoxString As String
        Dim x
        msgBoxString = "           " & Recipient(0)
        For x = 1 To UBound(Recipient)
        msgBoxString = msgBoxString & vbCrLf & "           " & Recipient(x)
        Next x
   '     msgbox "Mail was sent to:@@" & msgBoxString, vbInformation, Subject
'Clean Up
        Set Maildb = Nothing
        Set MailDoc = Nothing
        Set AttachME = Nothing
        Set Session = Nothing
        Set EmbedObj = Nothing
        
sendNotes_Err:
    If Err >= 7000 Then
    msgbox "Name was not found in the Lotus Notes directory." & vbCrLf & _
           "Check the name, and try again. (ex. 'Timothy S Bellomo')", _
           vbOKOnly + vbCritical, "Name Not Found!"
    SendSuccess = False
    Exit Function
    Else: SendSuccess = True
    End If
    
End Function

Keep in mind -- it works when executed, and thinks it works when scheduled, but doesn't.

If you can't see anything apparent, might you be able to suggest how to debug it?

Thanks,
Tim

 

[mrdenny]

Does the account that the SQL Server is running under have access to senda Lotus Note?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[tbellomo]

Hello again.

Previously, I had changed the setting from "Local account" to my username & password (which is the local account, isn't it?).

Normally, when I run this code in access, all that's required is that I have my Lotus Notes up and running.

Do you think I could hard code my username & password into the lotus note code to have it use an account I know will work?

Am I incorrectly assuming again that it will automatically use the account that is currently logged in?

Thanks,
Tim

 

[mrdenny]

SQL will never assume the role of the Logged in account. SQL is designed to be run without an account logged into the desktop. When working with SQL it's something that needs to be remembered.

Why not use a sendmail task in DTS to send the email, instead of using this code?

I'm not farmiller with the code needed to send mail using Lotus. I have always used exchage and send my mails using T/SQL.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[yksvaan]

I realize there are reasons you want/have to use this approach (I'm not that familiar with Notes), but I just remind that if server is running IIS with SMTP enabled and no other reason (security) in your system prevents it, you can send smtp-mail in a simple activex script. I refer to recent thread
Execute DTS from VB
thread961-945457

Cheers

 

[tbellomo]

I would have preferred to keep it the way it is, but c'est la vie.

I'm trying to configure MAPI with Lotus Notes to use SQL Mail...

Server isn't running IIS as well.

Thanks,
Tim