I am having an issue with an agent that is running when I step through the agent on my client but when I try to run it on the server it will not run properly. Also, we recently moved from an AS400 environment Notes on the AS400 working with DB2 files on the same AS400 to Domino being on a Windows 2003 server. The agent process and goes through the documents that need to be processed when running manually. When running on the server (scheduled) it appears to skip right over that step. Any help would be greatly appreciated. The connection is through ODBC. Here is the code:
Option Public
Option Declare ’ Force all Variable to be declared.
Uselsx “*LSXODBC”
Const ProdFlag = “Prod” ’ Change to “Prod” for Production
’ These constants will be assigned to the data source based on the above flag
Const prodmachine = “FLEET.UTLX.COM”
Const devmachine = “DEVELOP1.UTLX.COM”
Const prodlib = “UTCPRDFLS”
Const devlib = “STEELE”
Const prodctllib = “UTCPRDFLS”
Const devctllib = “STEELE”
Const ctrltable = “FILENAME”
Const headtable = “FILENAME”
Const dettable = “FILENAME”
Const odbcuser = “USERNAME”
Const odbcpwd = “PASSWORD”
Const HdrColList = " ( RNDCNE, RNZ9T2, RNAAT6, RNDDNE, RNDENE, RNDFNE, RNDGNE, RNRNCG, RNROCG, RNDKNE, RNADT6, RNAET6 ) "
Const DetColList = " ( RODCNE, RODHNE, ROAGT6 ) "
’ These are global varaibles
Dim session As NotesSession
Dim db As NotesDatabase
Dim v As NotesView
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Dim dt As NotesDateTime
Dim ret As Integer
Dim NumRecs As Integer
Dim counter As Integer
Dim TransID As String
Dim EndMsg As String
Dim EndMsg2 As String
Dim msg As String
Dim nl As String
Dim proccount As String
Dim replylog As NotesLog
Dim endmsg3 As String
Dim endmsg4 As String
'Dim newControlNum As Long
'Dim status As Variant
Dim form As Variant
Dim item As NotesItem
Dim odbcsource As String
Dim ProcDate As String
Dim ProcTime As String
Dim DB2CTable As String
Dim DB2HTable As String
Dim DB2DTable As String
Dim ControlNum As Long
Dim nName As NotesName
Dim nname2 As NotesName
Dim Recipients As String
Dim Subject, OIPType, OIPYear, SystemSource, FromID As String
Dim Seq1, Seq2, Seq3, Seq4, OIPSeq, Enviro As Integer
Dim Sender As String
Dim InString As String
Dim OutString As String
Dim RemainderString As String
Dim k As Integer
'These are the three ODBC database objects
Dim con As ODBCConnection
Dim qryctl As ODBCQuery
Dim resultctl As ODBCResultSet
Dim qryhdr As ODBCQuery
Dim resulthdr As ODBCResultSet
Dim qrydet As ODBCQuery
Dim resultdet As ODBCResultSet
Dim qry1 As ODBCQuery
Dim result1 As ODBCResultSet
Sub Initialize
If Not InitVariables Then Goto Done
' Begin Processing new Reply Messages
numrecs = 0
Print "Number of records equal 0"
Print "The next step is the DO statement"
Print "here is the Do Statement"
Do Until Doc Is Nothing '11/6/06
********* THIS IS WHERE IT WILL RUN MANUALLY BUT NOT ON THE SERVER… RIGHT AT THIS NEXT STEP.***********
If Not (Left$(doc.Status(0), 9)) = "Processed" Then
'Print "status" = doc.Status
Print "Check Control is beginning"
' Get the control number for a new inbound message
If Not CheckControl Then Goto Done
Print "We are going to start running the header"
' Write a header record into the header file
If Not WriteHeader Then Goto Done
Print "We are going to start running the detail"
' Write the detail records into the detail file
If Not WriteDetail Then Goto NextDoc
' Update the status of the document
Print "We are setting fields in the notes document to signify that the agent has processed the documents"
Set dt = New NotesDateTime(Now)
ProcDate = Format$(dt.DateOnly, "yyyy-mm-dd")
ProcTime = Format$(dt.TimeOnly, "hh.mm.ss")
doc.Status = "Processed" & "-" & ProcDate & "-" & ProcTime
doc.save True, False
' nl$ = Cstr(numrecs)
' Update our record processing count
numrecs = numrecs + 1
End If
' Set the Agent Processing flag and get the next doc
Print "We are going to get the next documents"
Call session.UpdateProcessedDoc(doc)
NextDoc:
' Close control result sets
resultctl.Close(DB_CLOSE)
resulthdr.Close(DB_CLOSE)
resultdet.Close(DB_CLOSE)
result1.Close (DB_CLOSE)
' Get next inbound message document to process
Set doc = v.GetNextDocument(doc)
Print "If there is another document, this is where it is getting the next document"
'Call ReplyLog.Logaction(nl)
Loop
' Disconnect from datasource
con.disconnect
EndMsg = "Completed processing " & Cstr(numrecs) & " reply messages."
Done:
' Write any processing messages to Notes Log
Print "TANK CAR INBOUND: " & EndMsg
If Not EndMsg2 = "" Then Print "TANK CAR INBOUND: " & EndMsg2
'Print "Connection" & EndMsg3
'Print "Error" & EndMsg4
End Sub
Sub ParseReplyText (InString As String, OutString As String, RemainderString As String)
'Dim WorkString As String
Dim i As Integer
On Error Resume Next
i = 70
OutString = Left$(Instring, i)
RemainderString = Mid$(InString, i+ 1)
If OutString = InString Then
RemainderString = ""
Exit Sub
End If
Do Until (Right$(OutString, 1) = " " Or Left$(RemainderString, 1) = " ")
i = i - 1
OutString = Left$(Instring, i)
RemainderString = Mid$(InString, i + 1)
Loop
'Call ReplaceSubstring(OutString, "'", "^~^")
'Call ReplaceSubstring(OutString, "^~^", "''")
End Sub
Function InitVariables As Integer
InitVariables = False
On Error Resume Next
Set session = New NotesSession
Set db = session.CurrentDatabase
Set v = db.GetView ("Replies")
Set con = New ODBCConnection
Set qryctl = New ODBCQuery
Set resultctl = New ODBCResultSet
Set qryhdr = New ODBCQuery
Set resulthdr = New ODBCResultSet
Set qrydet = New ODBCQuery
Set resultdet = New ODBCResultSet
Set qry1 = New ODBCQuery
Set result1 = New ODBCResultSet
con.SilentMode = True
con.AutoCommit = True
con.CommitOnDisconnect = True
' Define datasource based on constant ProdFlag
If ProdFlag = "Prod" Then
odbcsource = prodmachine
DB2CTable = prodlib & "." & ctrltable
DB2HTable = prodlib & "." & headtable
DB2DTable = prodlib & "." & dettable
Else
odbcsource = devmachine
DB2CTable = devctllib & "." & ctrltable
DB2HTable = devlib & "." & headtable
DB2DTable = devlib & "." & dettable
End If
' Write starting message to Notes Log
Print "TANK CAR INBOUND: Begin processing new tank car replies on " & odbcsource & "."
' Connect to data source
ret = con.ConnectTo(odbcsource, odbcuser, odbcpwd)
Print "Made connection to Fleet.UTLX.Com"
If Not ret Then
EndMsg3 = "Error connecting to ODBC source " & odbcsource & ": " & con.GetErrorMessage
EndMsg4 = "Extended error: " & con.GetExtendedErrorMessage
Print "Connection was not made to fleet.utlx.com"
Exit Function
End If
' Associate the queries with an ODBCConnection object
Set qryctl.Connection = con
Set qryhdr.Connection = con
Set qrydet.Connection = con
Set qry1.Connection = con
' Associate the result set with the query object
Set resultctl.query = qryctl
Set resulthdr.query = qryhdr
Set resultdet.query = qrydet
Set result1.Query = qry1
' Get the new replies to process in this database
'Set dc = db.UnprocessedDocuments
Set doc = v.GetFirstDocument
Print "It is now getting the first document"
InitVariables = True
End Function
Function CheckControl As Integer
On Error Resume Next
CheckControl = False
' First pick up the current control sequence number
qryctl.SQL = |SELECT * FROM | & DB2CTable & | WHERE F0UDCD='LOTUS'|
' Execute the query
ret = resultctl.Execute
Print "We are now getting the control number"
If Not ret Then
EndMsg = "Error cannot get control sequence number: " & resultctl.GetErrorMessage
EndMsg2 = "Extended error: " & resultctl.GetExtendedErrorMessage
Exit Function
End If
' Get the control number, increment it for use of this record, and write it back
controlNum = resultctl.GetValue("F0TGNB")
' Increment the control number value
Print "Control Number is Now" & controlNum
msg = "Control Number is Now " & ControlNum
'newControlNum = ControlNum + 1
'090806 Win2003 new statement
’ qry1.SQL = | UPDATE | & DB2CTABLE & | SET FOTGNB = FOTGNB + 1 WHERE FOUDCD = ‘LOTUS’|
QRY1.SQL = | UPDATE | & DB2CTABLE & | SET F0TGNB = F0TGNB + 1 WHERE F0UDCD = 'LOTUS' |
’ statusC =result1.SetValue(“F0TGNB”,newControlNum)
’ qry1.SQL = | UPDATE | & DB2CTABLE & | SET FOTGNB = ‘| & result1.GetValue (“FOTGNB”) & |’+ 1|
ret=result1.Execute
'ret = result1.UpdateRow
'Call resultctl.SetValue("F0TGNB", controlNum + 1)
' Update the row
ret = resultctl.UpdateRow
'ret = con.CommitTransactions
'qryctl.SQL = |SELECT * FROM | & DB2CTable & | WHERE F0UDCD='LOTUS'|
'ret = resultctl.Execute
controlNum = resultctl.GetValue("F0TGNB")
Print "Control Number is now" & controlNum
msg = "Control Number is Now " & controlNum
If Not ret Then
Print "Error Cannot write control Sequence number" & resultctl.GetExtendedErrorMessage
EndMsg = "Error cannot write control sequence number: " & resultctl.GetErrorMessage
EndMsg2 = "Extended error: " & resultctl.GetExtendedErrorMessage
Exit Function
End If
CheckControl = True
End Function
Function WriteHeader As Integer
On Error Resume Next
WriteHeader = False
With Doc
' OK now we have a control number for this new reply, let's insert a row to the header table
' Now let's set up the values for the new header record
Set nName = New NotesName(.From(0))
Recipients = ""
Forall names In .sendto
If names <> "Tank Car Messaging" Then
Set nname2 = New NotesName(names)
Recipients = Recipients & "; " & nname2.Common
'Recipients = Recipients & "; " & names
End If
End Forall
Recipients = Left$(Recipients, 127)
If Recipients = "" Then
Recipients = " "
Else
Recipients = Recipients
End If
Call ReplaceSubstring(Recipients, "; Tank Car Messaging", " ")
Call ReplaceSubstring(Recipients, " General Messages", " ")
If .Subject (0) = "" Then
Subject = " "
Else
Subject = Mid$(.Subject(0), 15, 63)
End If
If Isnull(.Seq1(0)) Then
Seq1 = 0
Else
Seq1 = .Seq1(0)
End If
If Isnull(.Seq2(0)) Then
Seq2 = 0
Else
Seq2 = .Seq2(0)
End If
If Isnull(.Seq3(0)) Then
Seq3 = 0
Else
Seq3 = .Seq3(0)
End If
If Isnull(.Seq4(0)) Then
Seq4 = 0
Else
Seq4 = .Seq4(0)
End If
If .OIPType(0) = "" Then
OIPType = " "
Else
OIPType = .OIPType(0)
End If
If .OIPYear(0) = "" Then
OIPYear = " "
Else
OIPYear = .OIPYear(0)
End If
If Isnull(.OIPSeq(0)) Then
OIPSeq = 0
Else
OIPSeq = .OIPSeq(0)
End If
If .SystemSource(0) = "" Then
SystemSource = " "
Else
SystemSource = .SystemSource(0)
End If
If Isnull(.Enviro(0)) Then
Enviro = 0
Else
Enviro = .Enviro(0)
End If
' Form the new SQL Insert query statement
qryhdr.SQL = |INSERT INTO | & DB2HTable & HdrColList & | VALUES ( | & controlNum & |, '| & recipients & |', '| & Subject & |', | & Seq1 & |, | & Seq2 & |, | & Seq3 & |, | & Seq4 & |, '| & OIPType & |', '| & OIPYear & |', | & OIPSeq & |, '| & SystemSource & |', '| & Enviro & |' )|
' Now insert the new row
ret=resulthdr.Execute
If Not ret Then
Print "TANK CAR INBOUND:" & EndMsg
EndMsg = "Error cannot write header record: " & resulthdr.GetErrorMessage
Print "TANK CAR INBOUND:" & EndMsg2
EndMsg2 = "Extended error: " & resulthdr.GetExtendedErrorMessage
Exit Function
End If
End With
WriteHeader = True
End Function
Function WriteDetail As Integer
WriteDetail = False
On Error Resume Next
With Doc
' OK now let's loop through the reply body text and write rows to the detail file
RemainderString = .ReplyBody(0)
k = 0
Do Until RemainderString = ""
k = k + 1
' Parse ReplyBody into discrete 70 char records
InString = RemainderString
Call ParseReplyText(InString, OutString, RemainderString)
' Insert new values into detail file
Call ReplaceSubstring(OutString, "'", "^~^")
Call ReplaceSubstring(OutString, "^~^", "''")
qrydet.SQL = |INSERT INTO | & DB2DTable & DetColList & | VALUES (| & controlNum & |, | & k & |, '| & OutString & |' )|
ret = resultdet.Execute
If Not ret Then
EndMsg = "Error cannot update detail file for message UNID " & doc.UniversalID
Print "TANK CAR INBOUND: " & EndMsg
EndMsg = "Error cannot update detail file: " & resultdet.GetErrorMessage
Print "TANK CAR INBOUND: " & EndMsg
EndMsg = "Extended error: " & resultdet.GetExtendedErrorMessage
Print "TANK CAR INBOUND: " & EndMsg
Exit Function
End If
Loop
' Now put final comments line in details database
OutString = "---------- Entered by: " & nName.Common & " on " & Cstr(.SentDate(0))
qrydet.SQL = |INSERT INTO | & DB2DTable & DetColList & | VALUES (| & controlNum & |, | & k+1 & |, '| & OutString & |' )|
ret = resultdet.Execute
If Not ret Then
EndMsg = "Error cannot update detail file for message UNID " & doc.UniversalID
Print "TANK CAR INBOUND: " & EndMsg
EndMsg = "Error cannot update detail file: " & resultdet.GetErrorMessage
Print "TANK CAR INBOUND: " & EndMsg
EndMsg = "Extended error: " & resultdet.GetExtendedErrorMessage
Print "TANK CAR INBOUND: " & EndMsg
Exit Function
End If
End With
WriteDetail = True
End Function
Function ReplaceSubstring(strValue, strFind, strReplace)
Dim location As Integer
location = Instr(1, strValue, strFind)
While location <> 0
strValue = Left(strValue, location - 1) & strReplace & Mid(strValue, location + Len(strFind))
location = location + (Len(strReplace) - Len(strFind))
location = Instr(location, strValue, strFind)
Wend
End Function