Recently we encountered a support call from a client where processing a large amount of Sales Ledger Invoices, i.e. > 2000 in a single batch, would encounter an OutOfMemoryException error. Given the volume of transactions, we calculated each transaction or invoice to be leaking or not releasing 700Kb of memory.
To replicate the issue, we set up the same import on our test environment and found we were encountering the same problem i.e. memory usage would grow and grow, until memory for the process was exhausted and the OutOfMemoryException error occurred.
I searched the Sage200 developer forum to see if anyone had encountered this issue; no-one had. I did however find, on a completely unrelated post, someone was forcing a full garbage collection using GC.Collect to release memory.
Nowhere in our IMan application have we had to explicitly manage memory in this way, but as we had nothing else we thought we’d at least try it out.
To make this efficient, we decided to collect every 200 invoices.
|
1 2 3 4 5 6 7 8 9 10 |
++i; if (i % 200 == 0) { //Wait for the thread to finish GC.WaitForPendingFinalizers(); //Force collection of any unused objects in memory GC.Collect(); } |
We re-ran the import, and voila, the memory issue disappeared, or more correctly memory usage would increase until GC Collect was called where then usage fell and available memory in windows would increase.
Whilst we had the problem solved, the solution had a foul stench about it, and we needed to the find the cause, whether it was our code or a Sage problem.
The first thing we did was to use Performance Monitor to query the various .net Memory performance counters for our application.
In the version of software which had the memory issue, two things stood out:
- ‘Gen2 Heap Size’ was massive; basically all memory was sitting in Gen2 waiting to be collected.
- ‘% of Time in GC’ was increasing over time until it spiked to 100% and the application subsequently crashed.
With the modified code using GC.Collect:
- Gen2 heap was reducing on each call.
- ‘% of Time in GC’ was remained relatively flat except for the spikes when we called GC.Collect.
What this was informing us was that we weren’t leaking memory, but there were a lot of objects accumulating in Gen2 waiting to be collected by the GC.
CLR Profiler
The next step was to identify which, or whose objects these were: our objects or Sage’s.
Whilst there are a lot of memory profilers available, the free CLR Profiler from Microsoft is actually pretty good. It provides a whole range of analysis but importantly it allows you to profile the .net garbage heap.
Using the Profiler, we decided to profile a batch of 200 invoices. After running we could immediately see a massive Gen2 Heap Size for a comparatively small number of invoices.
To find out which objects were sitting in the Gen2 heap, we viewed the Heap Graph.
Changing the detail to a relatively course mode, we could immediately see the majority of memory being used was indeed Sage objects waiting for their Finalizer to be run.
The Sage Response
We logged the issue with Sage after a little more testing to ensure it wasn’t our application and its running environment (under the hood we’re reasonably complex so I wanted to ensure the issue wasn’t related to that). Their response:
So Sage have exactly the same issue for some processes internal to Sage200. We’ve subsequently asked where the issue lay to which we’re awaiting a response.
General Issue
After further tests, we identified this issue applies to not only S/L Invoices but every Sage object inheriting from Sage.Common.DataAccess.BusinessObject i.e. any object used for data access in the Sage database.
Conclusion
We believe this is a fundamental bug/problem within the core of the Sage200 library, which needs resolving by R&D.
However, if you are processing large batches of any data using the Sage200 API, we highly recommend you implement a garbage collection mechanism to prevent ugly memory related errors.
Our issues show if you instantiate a Sage object in some sort of batch/continuous running process you will suffer a ‘memory leak’ and that object won’t be collected until you force collection.
In this blog we take a deep dive into the mechanics of using the Sage reporting engine in an external application using the Sage200 SDK for both single and multi-threaded applications. Topics covered:
- Correctly referencing the Sage reporting engine
- Using the Sage reporting engine within a service
- Use the Sage reporting engine in a multi-threaded application
- The Sage200 Security Context
- Multi-threaded use of the Sage200 Application object
Full source code is available at the bottom of the blog.
Background
When we added to IMan functionality to print various Sage200 forms we encountered a number of issues during development, deployment and use in a production environment.
This blog will attempt to uncover and present the solution to these issues. We hope that this will help other Sage developers who are grappling with the reporting engine complexities.
External printing with Sage Report Engine
The first phase to adding print support to our application was to provide support for printing forms such as invoices, picking lists, etc.
The biggest hurdle we faced was the correct referencing of various Sage libraries and dependencies.
References
- Copy Program FilesSage200 Unity.config to your local bin directory.
- Reference the following assemblies and ensure that NONE are copied to your local bin directory.
- Sage.Reporting.Services
- Sage.Reporting.Model
- Sage.Reporting.Engine.Integration
- Sage.Query.Engine.Model
- Sage.Query.Engine
- These assemblies are located in various places on a workstation with Sage200 installed; some are in the SDK, some are in the GAC, some are in the Sage installation directory. For those assemblies stored in the GAC we extract them using GAC View.
Setting the Printer at Runtime
At the time of writing (an enhancement request has been logged with Sage) the Sage Reporting Engine does not have any facility to set the printer at runtime. It can however be set by traversing the internal object structure of the ReportEngine object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
/// <summary> /// Prints a report to a specific printer or exports a report to a file. /// </summary> /// <param name="exportFilePath">The file to export the report to, can be empty or /// null if the report is being printed.</param> /// <param name="exportService">The IExportService i.e. this object.</param> /// <param name="flags">Should always be set to SuppressUserInteraction.</param> /// <param name="exportType">The type of file if the report is being exported or /// prn if being printed.</param> /// <param name="printerName">The local printer name if the report is being /// printed.</param> /// <returns>True...not sure of the signifigance as it's undocumented.</returns> private bool PrintReport(string exportFilePath, IExportService exportService, ExportFlags flags, ExportType exportType, string printerName) { Type exporterType = exportService.GetType(); PropertyInfo reportsPropertyInfo = exporterType.GetProperty("Reports", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance); IList reports = reportsPropertyInfo.GetValue(exportService, null) as IList; // Should never be zero, but just in case. if (reports.Count == 0) { throw new Exception("No reports in the layout file"); } // Get the ReportBatchItem var batchItem = reports[0]; // Get the report. var reportPropertyInfo = batchItem.GetType().GetProperty("Report"); // Get the actual report object. Sage.Reporting.Model.IReport report = reportPropertyInfo.GetValue(batchItem, null) as Sage.Reporting.Model.IReport; // Set the printer. // Sometimes the PrinterSettings can be null (usually when the report is // saved with a printer which is not installed/available under the user // profile which the report is being run. if (report.PaperLayout.PrinterSettings == null) { System.Drawing.Printing.PrinterSettings printSettings = new System.Drawing.Printing.PrinterSettings(); // Set the printer name before setting the object to the PrinterSettings // property. // Setting the object to the property fails, with the property remaining // null. printSettings.PrinterName = printerName; report.PaperLayout.PrinterSettings = printSettings; } else { System.Drawing.Printing.PrinterSettings printerSettings = report.PaperLayout.PrinterSettings; printerSettings.PrinterName = printerName; } } |
Using the Sage Reporting Engine within a service
Using any library within a windows service deserves special attention due to the various restrictions of the running environment, the Sage reporting engine is no different.
- ExportFlags.SuppressUserInteraction: Everything in a service needs to run attended therefore it is necessary to instruct the reporting engine not to display prompts or message boxes. Further, service security in Vista and Server2008 was tightened so that any service attempting to display any UI element is terminated by Windows.
- Sage.Accounting.DataModel.dll : Copy Sage.Accounting.DataModel.dll located in C:Users<userid>AppDataLocalSageSage200AssemblyCache to your service’s bin directory. As the name suggests, the Sage.Accounting.DataModel.dll assembly contains the Sage200 data model that is dynamically built by the Sage server whenever a model extension is installed (for instance when you add a new view or table to the database and run through the Custom Model Builder).
- Use locally installed printers: Services don’t load user profiles (irrespective of the service’s user); therefore they don’t have access to network printers (which are loaded dynamically as part of the user profile).
Using the Sage Reporting Engine in a multi-threaded application
This section deals with the threading issues experienced when our application attempted to run multiple instances of the Sage reporting engine concurrently.
Having completed the initial report printing development we deployed this to a couple of sites which were running without issue.
Our problem came on a new site where each time our application attempted to print Sage200 forms concurrently we would encounter a range of exceptions. These exceptions would essentially terminate the execution of one of the concurrently running integrations.
Background
Our IMan application is multi-threaded, meaning that each time integration runs within a separate thread of a single multi-threaded apartment (MTA) executable.
To this point we had not experienced any threading related problems with IMan. Multiple integrations could be run concurrently without issue. Further, we could successfully run multiple integrations or threads concurrently where each could create transactions within Sage200.
Problem
Under the hood the first thing Sage does when creating a ReportingEngine object is make a call to DoEvents (this infers the Sage reporting engine is written in VB.Net).
Because this ultimately makes a call to the Windows messaging queue, any interactions with the UI or Messaging queue should come from a single thread (marked as a Single Threaded Apartment (STA)) from within an application.
Windows does not permit multiple threads within an application to access the messaging queue, with an exception being raised by windows where multiple threads attempt to do so.
Why is an STA Thread Required? (Simplified)
http://blogs.msdn.com/b/jfoscoding/archive/2005/04/07/406341.aspxA fuller discussion of COM and threading models.
http://msdn.microsoft.com/en-us/library/ms693344(VS.85).aspxThe error below is the one generated when multiple threads of the same application attempt to create ReportingEngine objects at the same time.
The error is pretty misleading, and at the time we thought that we were dealing with a linking or referencing issue with some Office components.
Given that we hadn’t had a threading issue previously and that we had suppressed user interaction (above), we were left scratching our heads for a while.
The clue came when we looked at the callstack more closely: DoEvents and FPushMessageLoop both indicate some UI interaction is occurring.
System.InvalidCastException: Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘IMsoComponentManager’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{000C0601-0000-0000-C000-000000000046}’ failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).at System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 uReason, Int32 pvLoopData)
at System.Windows.Forms.ComponentManagerProxy.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.DoEvents()
at Sage.Reporting.Engine.Integration.ReportingEngine..ctor()
at Realisable.Connectors.Sage200.ReportExporter..ctor(String reportFileName, Dictionary`2 criteria, String exportFilePath, Boolean print, String printerName)
Our requirements
- Retain the multi-threadedness of Iman: Changing our architecture to single threaded was a non-starter.
- In-process: We did consider writing a separate print spool application, but this suffered from two problems:
- Marshalling the print requests and response between two applications was considered too fragile and difficult.
- Sage200 permits only a single login per user at a time. A separate process would have required a second login and Sage user.
Solution
The solution is a variant of the synchronised singleton pattern (this is in Java, but the pattern applies), where a single thread (print thread) processes report requests singularly, one at a time. Each subsequent print request is blocked until the print thread completes the executing print request.
Implementation
ReportQueueManager
The ReportQueueManager marshals the print requests to the Sage reporting engine and is also responsible for printer thread running state. It contains 4 methods:
- RunReport: Called by the worker threads to process a print request.
- ProcessReport: The internal method which de-queues and processes each request.
- Start & Shutdown: Respectively manage the startup and shutdown of the printer thread.
Printer thread
Internal to the ReportQueueManager class is a single threaded apartment (STA) thread which is responsible for processing each print request.
Inter-thread communication/synchronisation
To communicate between the calling/worker thread and the printer thread are two blocking queues:
- Request Queue: Accepts print requests from the worker thread.
- Reply Queue: Returns the success/failure of a print request from the printer thread back to the calling/worker thread.
The request queue is the key component for synchronising calls between the worker and printer threads. The queue is set to permit only a single request at a time i.e. its maximum length is set to one. When the queue is full (it has a request), all further requests are blocked; i.e. their execution is halted, thus permitting only a single request to be processed at a time.
To achieve the blocking queue we used a pattern as described here.
ReportRequest
The ReportRequest object encapsulates all the criteria such as report name, report criteria/parameters, printer name, etc. for a print job.
A ReportRequest object is created by a worker thread for each print request. The worker thread calls ReportQueueManager.RunReport, if the queue is empty, the request is processed immediately. However, if the queue is not empty (meaning a report is being printed), the calling thread is blocked.
Communicating success/failure to the caller
To communicate back to the calling thread if the print operation has succeeded or failed, a ReportReply object is created and placed onto the reply queue.[/tet]
Execution is returned when the ReportQueueManager de-queues the reply object from the reply queue. The reply is checked for success or failure, and where there is failure, it re-throws the exception.Understanding the Sage200 logon context
Our solution meant that each print request could be run against different Sage companies/databases, so a critical component in our solution was to get ReportEngine to target the correct Sage company.
If you have ever dealt with the Sage reporting engine, you would have noticed that there are no properties or methods for authentication or for targeting a specific company/database. This is because the reporting engine uses the current thread’s security context/principal (System.Threading.Thread.CurrentPrincipal).
Per MSDN, a principal object represents the security context of the user on whose behalf the code is running, including that user’s identity (IIdentity) and any roles to which they belong.
Sage200 sets the CurrentPrincipal as part of the set operation of the Application.ActiveCompany property.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
// Authenticate to Sage200 _application.Connect("admin", "admin"); // Set the active company. for (int i = 0; i < _application.Companies.Count; i++) { if (_application.Companies[i].Name == "Sage200_2010_DemoData") { // When the ActiveCompany property is set Sage sets the // current thread's CurrentPrincipal property with logon // context. _application.ActiveCompany = _application.Companies[i]; break; } } |
The solution requires that the print thread’s CurrentPrincipal is set to the principal of the thread.
Since the reporting and print threads are separate it is necessary to pass the print thread to the worker’s thread’s principal for each print request.
|
1 2 3 4 5 6 7 8 9 |
{ public ReportRequest() { //Capture the Principal of the calling thread Principal = Thread.CurrentPrincipal; //Used for debug and tracing purposes. ThreadId = Thread.CurrentThread.ManagedThreadId; } .... |
We do this by capturing the principal in the constructor of the ReportRequest class. When the ReportQueueManager de-queues the request it sets the printer thread’s CurrentPrincipal to the Principal of the request object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/// <summary> /// Worker method to process ReportRequests /// </summary> private static void ProcessReport() { // Run forever! while (true) { // This method will block until a request is placed onto the queue. ReportRequest request = _requestQueue.Dequeue(); // Set this thread's principal to the principal of the request, // which the reporting engine then uses. Thread.CurrentPrincipal = request.Principal; |
The result allows each calling/worker thread to target a separate Sage200 company e.g. worker thread ‘A’ targets company ‘Y’ and worker thread ‘B’, company Z.
It is worth noting that without this the Sage ReportingEngine object will raise an exception when CurrentPrincipal is not set (ref 1).
Sage.Accounting.Application objects are not threadsafe
During testing we kept encountering a DuplicateKeyFound exception when setting the ActiveCompany property whenever two threads entered the method simultaneously. Our solution was to wrap the code for the connect and disconnect in a lock statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
// The ActiveCompany property setter is not threadsafe, // wrap the code around a lock to prevent 'DuplicateKeyFound' exception. // _syncLock is a static object declared at class level. lock (_syncLock) { // Authenticate to Sage200 _application.Connect("admin", "admin"); // Set the active company. for (int i = 0; i < _application.Companies.Count; i++) { if (_application.Companies[i].Name == "Sage200_2010_DemoData") { // When the ActiveCompany property is set Sage sets the // current thread's CurrentPrincipal property with logon // context. _application.ActiveCompany = _application.Companies[i]; break; } } |
Summary
We would like to thank the Sage200 development support for all their assistance, and ESPI and the client for their patience.
Download Sage200 Report Manager source code(Ref. 1) This is not entirely true! We found during testing that if we had Sage200 open and did not set the printer thread’s current principal the ReportEngine would somehow use the SAAPrincipal of the open application. An exception was thrown when Sage200 was not open.