Coming soon: Your brand new Help Center & Community! Get a sneak-peek here

Issue with long running script after upgrading to SuperOffice 8.2

Hi All,

We have a script to update user defined fields on a project card, with total time spent on appointments stored against the project.  Depending on the appointment type, depends on which totals fields it updates.  The script run every night and loops through the projects using the following COM code.

Set objProjects = objSODB.Find.ProjectsByName("")

This has worked fine for years since SuperOffice 6.  However since the upgrade to 8.2 it runs for about 30 minutes, updating various projects before crashing with the following error.

The file as expected does exist.

It normally crashes around the same place (I have exported the project name it is updating to a text file so I can see where it crashes).  However the issue is not due to an error in any project in particular, because if I use ProjectsByName("CB"), then the projects it was crashing on that started with CB, all complete succesfully, the problem is with the volume of project being processed.

This has never been a problem pre 8.2, so is there an issue with long running Com scripts in SuperOffice 8.2.

Many thanks,

Trevor

Application

RE: Issue with long running script after upgrading to SuperOffice 8.2

Hi Trevor,
the short answer is no but I need more info and context.

My guess is that this is a external script using the database instance from SuperOfficeDB.dll, not the application instance.If this is so, then our application in-process script engine doesn't come into it. 

The resource error message makes me suspicious.
When the error message is shown, could you use a process explorer like TaskInfo to verify the paths to the loaded superoffice dlls. I think you need to find the process that is running the script engine.
I just want to eliminate a possible COM registration issue where your environment now is confusing 2 installations.
If this doesn't tell us anything then I will need to see some code to have something to think about.

Conrad

Af: Conrad Weyns 24. jan 2018

RE: Issue with long running script after upgrading to SuperOffice 8.2

Hi Conrad,

Thanks for the reply, below is the screenshot of ProcessMonitor, at the point the error occurs.

It looks like it is using the correct dlls, and the script does work for about 30 mins before failing.  Once it does fail, if you click on the error message, it goes away for a few seconds, tries to update the next project and errors again.

By the point it does fail, it is utilising a considerable amont of RAM, but there is still plenty available on the server.

Trevor

Af: Trevor Sharp 25. jan 2018

RE: Issue with long running script after upgrading to SuperOffice 8.2

Ok thanks. My gutt feeling is that we hit on some error, possibly an uncaught exception after which all betts are off.
If ram keeps on growing then that wories me but it depends on the script code.
I think I need to try and reproduce it with a script that contains the basics of yours.
Could you give me some idea of your code so that I can write a test that closely resembles it.

/conrad

Af: Conrad Weyns 25. jan 2018

RE: Issue with long running script after upgrading to SuperOffice 8.2

Thanks Conrad.  I will look to create a cut down version of the script, in the test enviroment, and see if I can get the error to occur in the test enviroment, then I will send the script across.

I should be able to do this tomorrow.

Many thanks,

Trevor

Af: Trevor Sharp 25. jan 2018

RE: Issue with long running script after upgrading to SuperOffice 8.2

Hi Conrad,

I have done some more testing, restoring our live database to my virtual machine with SuperOffice 8.2 on it.  I can recreate the issue in my test enviroment, which I think is good as it rules out enviroment.

I did have a few interesting finds though.  Below are a list of the number of projects in our system, based on the first letter of the project name.

Project  NoOfProjects
1           1
2           1
3           1
7           1
A           43
B           50
C           86
D           30
E            41
F            11
G            5
H           15
I             22
J            34
K           13
L            25
M           60
N           24
O           32
P           103
Q           8
R           18
S           117
T           42
U           2
V           10
W           6
X           3
Total      804

The full script update the project udefs based on three things.

- Total time spent on appointments against the project

- Total time spent on CS tickets against the project

- Last activity date of a completed SuperOffice appointment against that project.

As it loops through each project it runs various queries to get this information, and this has worked fine for a number of years.

If I leave all of the code in I get about as far as half way through the projects starting with the letter "C".  If I remove the CS ticket time from the code, I get to about "G", before it errors.  If I remove the SuperOffice appointment time I get to "O", and if I remove the Last Activity date check if completes the script without any issues.

So it does seem to be a build up in resource that is causing the error.

I have copies of both the full script and the cut down script.  Also if you cannot recreate this on your db (you would need to create 2 udefs on the project for the cut down version of the script), I can provide you with a copy of our database.

Is it ok if I email the scripts over to you?

Many thanks,

Trevor

Af: Trevor Sharp 26. jan 2018

RE: Issue with long running script after upgrading to SuperOffice 8.2

Hi Trevor,

Can you show me the code that computes the data that gets set in UDef felds?
Is OleDb provider invoilved?

/conrad

Af: Conrad Weyns 26. jan 2018

RE: Issue with long running script after upgrading to SuperOffice 8.2

Hi Conrad,

Below are the functions to get the appointment hours for the project, and the last activity date.

Sub GetAppntHoursForProject(Database, ProjectID, CompletedStatus, TotalDevelopmentDuration, TotalTrainingDuration, TotalOtherDuration, ResetTotals)

Dim con, rs, SQLQuery, Duration

'Reset Flags
DevelopmentDuration = 0
TrainingDuration = 0
OtherDuration = 0

If ResetTotals = True Then
 TotalDevelopmentDuration = 0
 TotalTrainingDuration = 0
 TotalOtherDuration = 0
End If

 'Get SuperOffice Conntection String
 Set con = CreateObject("ADODB.Connection")
 con.Open Database.GetADOConnectionString

 SQLQuery = "SELECT Appointment.Do_By, Appointment.EndDate, Appointment.Task_idx, long07, long05, double05 FROM Appointment, Associate, Udappntsmall " _
   & "WHERE Appointment.associate_id = Associate.associate_id AND Associate.type = 0 AND userdef_id *= udappntsmall_id AND document_id = 0 AND project_id = " & ProjectID

 'Complete SQL Query Depending On Search Requirements
 SQLQuery = SQLQuery & " AND Status = " & CompletedStatus

 'Msgbox SQLQuery

 'Run Query
 Set rs = con.Execute(SQLQuery)


 'Move To First Record
 If Not rs.Eof Then rs.MoveFirst


 'Loop Through All Records
 Do Until rs.Eof

  'If Appointment Set To Billable
  If rs("long07") = 1 Then
  
   'If Task Type Is Not Overridden
   If rs("long05") = 0 Then
    'If Development App Type
    If CheckApptTypes(Database, rs("Task_idx"), DevelopmentTaskNames) = True Then

     'If Duration Overridden
     If rs("double05") <> 0 Then
      'Get Overridden Value
      DevelopmentDuration = DevelopmentDuration + rs("double05") * 60
     Else
      'Else Total Up Duration Field
      If Not IsNull(DateDiff("n", rs("Do_By"), rs("EndDate"))) Then
       DevelopmentDuration = DevelopmentDuration + DateDiff("n", rs("Do_By"), rs("EndDate"))
      End If
     End If
    Else
     'If Training App Type
     If CheckApptTypes(Database, rs("Task_idx"), TrainingTaskNames) = True Then

      'If Duration Overridden
      If rs("double05") <> 0 Then
       'Get Overridden Value
       TrainingDuration = TrainingDuration + rs("double05") * 60
      Else
       'Else Total Up Duration Field
       If Not IsNull(DateDiff("n", rs("Do_By"), rs("EndDate"))) Then
        TrainingDuration = TrainingDuration + DateDiff("n", rs("Do_By"), rs("EndDate"))
       End If
      End If
     Else
      'If Any Other Appointment Type
      'If Duration Overridden
      If rs("double05") <> 0 Then
       'Get Overridden Value
       OtherDuration = OtherDuration + rs("double05") * 60
      Else
       If Not IsNull(DateDiff("n", rs("Do_By"), rs("EndDate"))) Then
        OtherDuration = OtherDuration + DateDiff("n", rs("Do_By"), rs("EndDate"))
       End If
      End If
     End If
    End If
   Else
    'If Development App Type
    If rs("long05") = 843 Then
    
     'If Duration Overridden
     If rs("double05") <> 0 Then
      'Get Overridden Value
      DevelopmentDuration = DevelopmentDuration + rs("double05") * 60
     Else
      'Else Total Up Duration Field
      If Not IsNull(DateDiff("n", rs("Do_By"), rs("EndDate"))) Then
       DevelopmentDuration = DevelopmentDuration + DateDiff("n", rs("Do_By"), rs("EndDate"))
      End If
     End If
    Else
     'If Training App Type
     If rs("long05") = 845 Then
     
      'If Duration Overridden
      If rs("double05") <> 0 Then
       'Get Overridden Value
       TrainingDuration = TrainingDuration + rs("double05") * 60
      Else
       'Else Total Up Duration Field
       If Not IsNull(DateDiff("n", rs("Do_By"), rs("EndDate"))) Then
        TrainingDuration = TrainingDuration + DateDiff("n", rs("Do_By"), rs("EndDate"))
       End If
      End If
     Else
      'If Any Other Appointment Type
      'If Duration Overridden
      If rs("double05") <> 0 Then
       'Get Overridden Value
       OtherDuration = OtherDuration + rs("double05") * 60
      Else
       If Not IsNull(DateDiff("n", rs("Do_By"), rs("EndDate"))) Then
        OtherDuration = OtherDuration + DateDiff("n", rs("Do_By"), rs("EndDate"))
       End If
      End If
     End If
    End If
    
   End If
  End If

  'Move To Next Record
  rs.MoveNext
 Loop

 'Return Total Duration In Hours To 2 Decimal Places
 TotalDevelopmentDuration = TotalDevelopmentDuration + Round(DevelopmentDuration/60, 2)
 TotalTrainingDuration = TotalTrainingDuration + Round(TrainingDuration/60, 2)
 TotalOtherDuration = TotalOtherDuration + Round(OtherDuration/60, 2)


 Set rs = Nothing
 Set con = Nothing
End Sub


'----------------------------------------------------------------------------------------------------------------------------


Function GetAppointmentDateOfProjectLastActivity(Database, ProjectID)

Dim con, rs, SQLQuery, ReturnValue

 ReturnValue = ""
 
 'Get SuperOffice Conntection String
 Set con = CreateObject("ADODB.Connection")
 con.Open Database.GetADOConnectionString

 SQLQuery = "SELECT Appointment.Do_By FROM Appointment WHERE document_id = 0 AND project_id = " & ProjectID & " AND Status = 3 ORDER BY Appointment.Do_By"

 'Msgbox SQLQuery

 'Run Query
 Set rs = con.Execute(SQLQuery)

 'Move To First Record
 If Not rs.Eof Then
 
  rs.MoveFirst
  ReturnValue = rs("Do_By")

 End If

 Set rs = Nothing
 Set con = Nothing
 
 GetAppointmentDateOfProjectLastActivity = ReturnValue
 
End Function

Many thanks,

Trevor

Af: Trevor Sharp 26. jan 2018

RE: Issue with long running script after upgrading to SuperOffice 8.2

Hi Trevor,
this is just a hunch.
Since you have a test environment up and running and this will take me some time try and reproduce, could you make one change and give it another run: 
Close both the resultset and the connection every time:

rs.Close
Set rs = Nothing

con.Close
Set con = Nothing

Just in case you should observe some differences.
Conrad

Af: Conrad Weyns 26. jan 2018

RE: Issue with long running script after upgrading to SuperOffice 8.2

Hi Conrad,

I have tested again, added the rs.close and the con.close, but unfortunately I got the same error.  One time I ran it though (as I ran it a few time to test), I got the error below.