Stephen Smith's Blog

Musings on Machine Learning…

Archive for March 2011

Moving VB Screens to the Web

with 6 comments

Now that 6.1A development is ramping up and we are on the journey, I thought I’d blog in a bit more detail on how we are moving the VB screens to the Web. In order to complete the Accpac journey from VB to the Web we have to convert thousands of screens. In order to do this we have developed a number of tools to help in the process. However in order to reach our usability and performance objectives, a certain amount of hand tuning is still required.

After the TPAC conference we had a developer open house, where third party developers could come visit with our developers and tour our facility. We provided a number of presentations including one on using our Porting tools and on our Porting process, including how we want to improve our usability. These tools are now in the SDK which is posted on the development partner Wiki and the various porting guidelines are being posted there as they become finalized.

Porting Tool

Our Porting tool consists of two parts. The first is a Visual Basic plug-in. This plug-in takes a list of VB Projects, loads each one and then iterates through the various collections in the project and writes them out to an XML file. Below is the screen you get when you run the Porting Wizard from VB:

This tool needs valid Accpac Sign-on information since it actually loads and activates each UI. This allows it to load all the language specific strings so these can be ported as well. Basically when run it outputs all the information on the screens (including popups), all their controls, all language strings and their layouts. As a VB plug-in this tool can find and export any design time or runtime information from the VB projects.

The next step is to take this XML file and create a SWT (Sage Web Toolkit) project from it. Our tool for creating SWT projects is SwtUICreator. This tool can either be run command line or from an Eclipse plug-in. To convert UIs it needs to be run from the command line. Usually you would create a batch file to convert all your UIs. For instance the following will create a SWT project for Order Entry based on the OE1100AccpacCtlInfo.xml file generated by the VB Wizard mentioned above.

swtuicreator.cmd –accpacui
       -uiid oe1100
       -uiversion oe61a
       -productid Accpac
       -companyname sage
       -libdir “C:\Pluswdev\SWT\lib”
       -gwtdir “C:\dev\google\gwt-2.2.0”
       -out “C:\dev\PortingTools\out”
       -accpacctlinfo “C:\dev\PortingTools\AccpacCtlInfo\OE1100AccpacCtlInfo.xml”
      -copyright “2011 Sage Inc. All Rights Reserved”
      -overwrite

This will then create a SWT project with the screen layout converted, SData resources defined and is ready to run.

The following screen shot is that A/R Customer UI straight from the porting tool. It should look somewhat familiar if you are used to the VB screen. Notice that it has converted the notebook tabs into disclosure panels and has changed the record navigation controls to upper right of the screen. We haven’t finished updating the Cascading Style Sheet (CSS) for this yet, so the look will change (and hopefully improve quite a lot).

There is quite a bit of room for tuning this UI to look much better. Developers now have as a starting point a screen that can navigate, create, update and delete records. All the fields are connected to datasource properties and all the translated strings have been moved from VB language DLLs to XML strings definition files.

After the Porting Tool produces the starting point, people start modifying the code to produce the final product. But what if we improve the porting tool? With-in the porting tool we are providing the facility to re-run it, but not to overwrite any changes made since last time. So we can start porting without worrying that we will have to start over. For instance we are planning to generate automated test scripts from the porting tool, but porting has already begun. So the porting tool will be able to add these automated tests to existing projects later.

Porting Guidelines

The porting guidelines are basically a set of rules of how we want the various design patterns used in the VB screens to be converted to the Web.

The porting tools will try to apply as many of these as possible, but it can only work with what it has available in VB. So the next step is to apply any tweaks to the UI that are necessary in the SWT UI Designer to get the screen to follow the porting guidelines. For most simple screens, no work is necessary. But as screens get more complex then more hand tuning is required.

For the main document entry screen like Order Entry where we expect many users to spend the majority of their time, then there will be custom usability research applied to go beyond the straight porting process.

The developers take the screens output from the Porting Tool, make any necessary tweaks to follow the porting guidelines, then get a usability expert to sign off on the screen design. Then they write any necessary code.

Coding

Although the Porting Tool makes a good start, there is often much more to a UI than just basic CRUD operations on simple records. The developers now have to add code to support all the things that go beyond this. They need to hook up all the popup forms correctly. They need to ensure the right things happen on fields that have a lot of logic behind them, like entering a customer number in Order Entry. On simple setup screens there probably isn’t any coding required. But on complicated document entry screens, there is a lot of code going on behind the scenes.

Performance

If we just run the screens as produced directly from the porting tool then they are quite slow. This is because the porting tool ports all the VB datasource to SData datasources. But each SData datasource requires calls to the server to configure and get data. We don’t want multiple of these happening. Ideally each screen should use very few datasources (ideally one). If a VB UI requires something, it just asks for it, this is a problem on the web since we want to reduce the number of round trips to the server. Often a UI wants to look up ancillary helpful information to help users, which is good, but we don’t want a separate server request to do this. So we have to remove these extra datasources from the project and add this information to the main SData feed for the project. This is relatively easy since it’s just a matter of editing the XML definition for the main SData feed to define what extra information to include (like G/L Account descriptions).

Framework

From the A/R Customers screen above you can see that some of the controls like the record navigation control are still in the early stages. We are starting moving all the VB screens to the Web while still completing the framework so we can get feedback to ensure the SWT framework supports all the things we require. We made sure all the APIs were competed so we won’t have to keep making changes to the UIs as we finish the framework. We left some of the cosmetic affects and some of the usability tweaks to be completed later. This is to get as much feedback as possible as early as possible so we still have plenty of time to make adjustments.

Benefits

The main benefit of this project is to move to the Web. To reduce TCO by removing the need for any sort of Workstation Setup on a computer that is using Accpac. As we move to 6.1 we are supporting the IE, Firefox, Safari and Chrome browsers. This means you can run Accpac from any device/computer that supports one of these Browsers. This includes regular PCs, Linux PCs, Macs, iPads and Android Tabs. Additionally smaller versions of the screens can run on devices like iPhones and Android phones.

As we move all the screens to the Web we are looking to improve usability and address various customer complaints about the screens. For instance people have complained about optional fields always being in popup forms. Now they are either in disclosure panels or are actually in the grid control as additional columns rather than requiring a popup. People have complained about the notebook tabbed panels we use in VB, that it is too time consuming switching between tabs and that you can’t see all your information at once. Now with disclosure panels you can leave them open or closed as you wish depending on what you want to see (and the state is remembered from use to user). When entering comment/instructions you had to enter several fixed length lines which made copy/paste difficult. Now these are a single field so you can easily cut and paste the data.

Another complaint is that when you open a report, it is model, so it’s hard to open different versions of a report with similar parameters. Now each report opens in a separate Portal panel and you can open as many as you want to compare data much easier.

Summary

Now that we are getting into full swing moving all our screens from Accpac to the Web, I hope this gives an idea of the process that we are following. As the various Accounting Modules start to come together we’ll post them on an externally facing Web server so people can see them and provide feedback as we develop them.

Written by smist08

March 26, 2011 at 5:16 pm

Accpac Payment Processing

with 5 comments

I blogged about our plans for adding credit card processing to Sage ERP Accpac here. At that point we had the general design worked out and we were just beginning development. Now development has come along quite a way, and we demo’ed the product to partners at TPAC. We received a lot of good feedback. I gave the presentation, but in many regards the knowledge of the product and credit card processing in the audience was much greater than my own. So a few questions were left un-answered and several good suggestions were made. It is always fun to give a presentation where the audience is so involved and passionate about the topic. Several of the points brought up were very good and are being incorporated into the product. This is the good thing about our Agile development process, that we can continually get feedback from clients and partners and incorporate it into the product as we develop it. The goal of this blog posting is to:

  • give more details on our payment processing solution
  • provide some screenshots
  • answer the several questions that I couldn’t answer during the session
  • list the suggestions that we are going to incorporate into the product before release
  • re-iterate any features that are deliberately not being included.

Accpac Payment Processing Walkthrough

Now that Payment Processing is working and in the later stages of development, we can walk through the process with a few screenshots. Keep in mind that some of the labels, wording and screens will change a bit before final release.

Setup

To setup Payment Processing you just need to sign up with Sage Payment Solutions to obtain a merchant account and then enter that into the Accpac Payment Processing Setup screen:

As pointed out by several people, having just one of these is insufficient and we will change this screen to accept multiple IDs. Next you need to setup some A/R Payment Codes with the new Payment Type of “SPS Credit Card”:

The various credit card processing functions will only be available when the document you are dealing with has this payment type.

If you have customer credit cards on file, you can enter them into the A/R Customers screen which now has a Credit Card tab. You can enter as many cards as you like on this screen. You can set one as the default card. The credit card numbers aren’t stored in Accpac, they are stored in the Sage Payment Services vault on a secure server in their facility. The Credit Cards aren’t handled by Accpac nor are they stored anywhere on customer’s own computers.

Entering an Order and Performing a Pre-Authorization

Below is the Order Entry screen where an order has been entered and we now want to pre-authorize the card, so we can reserve credit limit for when we charge the card when we ship the product. So the user has pressed the Pre-authorize button and get this screen:

Then you enter the Payment Code and adjust the amount (you may want to raise it if you anticipate additional costs like shipping and handling, or lower it if this is just a deposit). Then you get the Pre-authorize processing screen where you can verify the credit card information before charging the card.

When ready you hit the Process Pre-authorization button and control is transferred to Sage Exchange which then puts up the screen that will really affect the Credit Card.

Then when you hit Submit the Pre-authorization will be processed. According to credit card processing rules you aren’t supposed to charge credit cards until you actually ship the product. So when you ship the product you will now follow the same sort of procedure, basically applying an O/E Pre-payment to the Order/Shipment/Invoice following the same sort of process outlined above.

Feedback and Questions from TPAC

This section outlines some of the questions and concerns that were brought up at the TPAC session. Basically to answer any outstanding questions and to highlight the feedback that is being incorporated into the product.

More than 1 Merchant ID. I need more than one Merchant ID per currency. I might have different Merchant IDs by geographic location or I might have different Merchant IDs for transactions of different risks such as card present versus over the web. We are adding this feature to the product.

Capture Invoice Amount. Change the default amount on capture to be the invoice amount rather than the pre-authorization amount. Often the pre-authorization covers unexpected costs and is on the high side. We will incorporate this change into the product.

Notify if Pre-payment Already Made. When a partial shipment occurs for an order that was originally paid via credit card, how does the shipper know that the backfill shipment now needs to be charged? This issue negatively impacts any company that creates backorders, which is a significant percentage of our base. We will incorporate this change by displaying a warning message that a prepayment already made by credit card. Option presented to process card payment; if selected common popup defaults original card used for first shipment

Refund to different type. Need to refund to something other than the credit card that made the transaction. Perhaps this credit card was lost, stolen or cancelled.  We will now allow refund to other payment types such as check. We will not allow a refund to a different credit card due to credit card compliance issues.

Disallow capture on shipment. Is it a problem having the a credit card capture on the shipment screen since the shipment can be deleted or not saved after the credit card is charged?  We will catch this during day end processing and flag it, there is also a full audit trail from the virtual terminal, but there seems to be sufficient concern that if you capture during shipment we will force you to generate the invoice in order to capture the card.

How to separate credit cards for Bank reconciliation? If you really need to keep things completely separate you will need to use separate Banks which means only doing one type of credit card per A/R Receipt batch. You can track things separately by using different A/R Payment Codes, but there isn’t a way to separate these in Bank reconciliation and they don’t affect the G/L distribution. One thing to note is that you can reconcile deposits by detail again in Accpac 6.0A PU1, so you don’t need to do the whole deposit at once if statements come in at different times. Also in reconciliation if there are lines in the deposit with the “SPS Credit Card” payment code then this enables you to enter credit card charges. Also remember there is the Deposits Register which gives detailed information on the data being deposited from the sub-ledgers.

Multiple Pre-authorizations. I need to do multiple pre-authorizations on an order to different credit cards (because the client doesn’t have enough credit limit on one card)? We don’t support this, if you really need this then you will need to use an ISV credit card product. We do support charging multiple cards for an order, so you can charge three cards to pay the amount, but you can’t pre-authorize more than one card. Another note is that each charge has to be on a separate pre-payment, you can’t have multiple charges on a single pre-payment.

Different Processor. I need to use another processor than Sage Payment Services? Sorry we only support the SPS processor.

International. Sage Exchange only supports transactions in CAD or USD from Canada or the US. This is a current limitation, if you require additional countries or currencies then you need to use and ISV product.

Summary

The goal of the Accpac Payment Processing module is to provide easy credit card processing with functionality that will satisfy most customers. We aren’t looking to build a large module that can solve every possible scenario. For these we have an active ISV community that either has much larger solutions or will provide customizations and extensions to our core offering. We are trying to keep our solution simple so it works well for most customers, that it is easy to use and will be well adopted. Often complexity, although theoretically it can address more cases, tends to be more of an obstacle to adoption since the majority of customers with straight forward requirements find it hard to figure out or burdensome to use (due to all the options).

Written by smist08

March 19, 2011 at 4:44 pm

RVSpy and DBSpy

with 5 comments

For anyone that has dealt with customer support on a thorny Sage ERP Accpac application issue, you have probably been asked to provide a RVSpy and/or a DBSpy log. What are these logs? Can I use these tools to diagnose problems myself? These are the topics we’ll be covering in this blog posting.

Log files are typically generated by programs to produce a record of what has happened and all the steps along the way. These are useful for diagnosing problems since engineers can study these to see, not only what the actual problem was, but what were the steps that led up to the problem.

Below is the usual Architecture diagram for Sage ERP Accpac. Basically it is a three tier architecture with a thin API into each tier.

This is the basis for the DBSpy and RVSpy programs. DBSpy spies and reports on all calls made to the Database Services layer. RVSpy spies and reports on all calls made to the Common Business Logic API. Thus these tools can give a complete picture of everything going to and from the Database Layer (including failure codes and diagnostic message) as well as everything going to and from the Business Logic Layer.

We originally included these as part of the SDK back when version 1.0 was released, but found these tools so useful, and were giving them out so often, that we included them into the regular product.

For further information on troubleshooting, check out these blog postings on diagnosing problems: part 1part 2part 3 and part 4.

DBSpy

To run DBSpy, go to Start Menu – All Programs – Sage Accpac – Tools – DBSpy. This will bring up:

In the options for this program you can control where you want to log the output to. For longer running programs you might want to only log to a file and turn off the logging to a window, since it can slow your system down scrolling all this information past.

You can actually get more information out of DBSpy by configuring it with a utility called logAdder:

If you check the various boxes here you can get more info in the log files. If you are getting a strange database error with no output in the DBSpy log file then check everything here and try again. Often the more detailed logging will give you the answer. This tool is given out by customer support. If you turn on these options, make sure you turn them off again after you are done as generating all these log strings can slow down processing.

Usually you run DBSpy, reproduce your problem and immediately stop DBSpy, so the problem will be near the end of the log file. Then you go back from the end to see what statements failed or what additional error diagnostics are logged. If you see the error code: DBS_NO_MORE_DATA, this probably just means the program was scrolling through a range of records and got to the end. This error return rarely indicates a problem. If you see the error code: DBS_NOT_FOUND, this means the program tried to read a record and it wasn’t found. This may be ok, since the program might just be seeing if an optional feature needs processing, but it might also be a validation error meaning a required record is missing and this is the source of your problem. Other error returns are usually real problems.

For a bit more information on the Accpac Database Layer, check out this posting. Most of the output you see in the logfile corresponds to the various functions in our Database API, things like OPEN-TABLE, SELECT, FETCH, etc. From the routine names you can get an idea of what sort of database operations are going on. If you are running SQL Server or Oracle, these will cause SQL statements to be generated and executed, which you can see in ODBCTrace. For Pervasive.SQL they may produce SQL statements or calls to the Pervasive transactional engine. There isn’t a one to one correspondence between our Database API and SQL statements so one call may cause several statements to be executed and some calls don’t cause any SQL to be executed.

RVSpy

RVSpy records all API calls to the Accpac Views (Business Logic Objects). This includes all SDK produced applications whether from Sage or from ISVs. Often one View will call other Views, and this is all recorded and nested inside the log file or output Window. There is also an option to include a DBSpy inside the RVSpy so you can see what View calls generated which Database calls. You run RVSpy from the Start Menu – All Programs – Sage Accpac – Tools – RVSpy. Below is the RVSpy program:

Again you can configure the output whether you want it to the RVSpy Window and/or to a file. Beware that by default you won’t have permission to write it to C:\ so you either need to grant yourself permission or put it somewhere else. One tip is to turn off some of the field level API calls since these are quite noisy and tend not to give useful information such as: Field, Fields, Attribs, Type, Name, Key, Keys, Presents and event Get. This then speeds up logging quite a bit and makes it easier to read the log file.

Views are referenced by their ID and name such as IC0580: ICREED in the above screen shot. You can get additional information on the Views by looking them up on the Application Object Model (AOM) on our website.

There is a standard set of View API calls such as Get, Put, Fetch, Verify, Update, Insert, Read. You see each of these go by with their parameters and return code. Each of these will always return an error code. 0 means success. The standard return codes are:

//////////////////////////////////////////////////////////////////////////////
// Error codes. There are two sets: old and extended. These can co-exist
// quite well since they don’t overlap. Some of the old codes are made
// redundant in the extended codes; these are prefixed with “OLD_”.

// Old error codes

#define  ERRNUM_SUCCESS                   0
#define  ERRNUM_LOAD_FAILED               100
#define  ERRNUM_OPEN_FAILED               101
#define  ERRNUM_COMPOSE_FAILED            102
#define  ERRNUM_ROTOENTRY_FAILED          103

#define  OLD_ERRNUM_GENERAL               1
#define  OLD_ERRNUM_WARNING               10
#define  OLD_ERRNUM_OTHER                 2

#define  OLD_ERRNUM_RECORD_NOT_FOUND      1
#define  OLD_ERRNUM_RECORD_NO_MORE_DATA   1
#define  OLD_ERRNUM_RECORD_EXISTS         1
#define  OLD_ERRNUM_RECORD_DUPLICATE      1
#define  OLD_ERRNUM_TABLE_EXISTS          1

// Extended error codes

#define  ERRNUM_WARNING                   WARNING_GENERAL
#define  ERRNUM_GENERAL                   1000

#define  ERRNUM_RECORD_NOT_FOUND          1020
#define  ERRNUM_RECORD_NO_MORE_DATA       1021
#define  ERRNUM_RECORD_EXISTS             1022
#define  ERRNUM_RECORD_DUPLICATE          1023
#define  ERRNUM_RECORD_INVALID            1024
#define  ERRNUM_RECORD_LOCKED             1025
#define  ERRNUM_RECORD_CONFLICT           1026
#define  ERRNUM_RECORD_NOT_LOCKED         1027
#define  ERRNUM_RECORD_PROTOCOL           1028
#define  ERRNUM_TABLE_EXISTS              1040
#define  ERRNUM_TABLE_NOT_FOUND           1041
#define  ERRNUM_PERMISSION_NONE           1060
#define  ERRNUM_MEMORY_NO_MORE            1080
#define  ERRNUM_MEMORY_BUFFER_LIMIT       1081
#define  ERRNUM_FILTER_SYNTAX             1100
#define  ERRNUM_FILTER_OTHER              1101
#define  ERRNUM_KEY_INVALID               1120
#define  ERRNUM_KEY_NUMBER                1121
#define  ERRNUM_KEY_CHANGED               1122
#define  ERRNUM_FIELD_INVALID             1140
#define  ERRNUM_FIELD_NUMBER              1141
#define  ERRNUM_FIELD_INDEX               1142
#define  ERRNUM_FIELD_DISABLED            1143
#define  ERRNUM_FIELD_READONLY            1144
#define  ERRNUM_TRANSACTION_NONE          1160
#define  ERRNUM_TRANSACTION_OPEN          1161
#define  ERRNUM_REVISION_PROTOCOL         1180
#define  ERRNUM_DATABASE_PARAMETER        1200
#define  ERRNUM_DATABASE_LIMIT            1201
#define  ERRNUM_DATABASE_OTHER            1202
#define  ERRNUM_DATABASE_DICTIONARY       1203

#define  ERRNUM_RPC_FAILURE                                                   1220 // remote procedure call had communications failure

#define ERRNUM_APPLICATION_DEFINED_BASE              9000
#define ERRNUM_APPLICATION_DEFINED_END               9999

// Extended warning codes

#define  WARNING_GENERAL                  -1
#define  WARNING_FILTERDELETE_ORPHANS     -10
#define  WARNING_FILTERCOUNT_APPROXIMATE  -20

#define WARNING_APPLICATION_DEFINED_BASE            -1999
#define WARNING_APPLICATION_DEFINED_END             -1000

The Views can also define custom error codes that you will see now and then.

Tips to Analyzing Logs

Customer support usually asks for these logs, when they are going to escalate an issue to development, because development will always ask for them. Development can take the logs and look in the source code to get additional insights into what may be going on. You don’t have access to the source code, but often you can still get clues as to problems. Also remember to run the Accpac Data Integrity Checker as this will often uncover issues to do with database relational integrity.

First stop logging when the error occurs. Then the problem will be near the end of the log. When an error occurs often something will fail when it happens and then everything that called it will fail. So at the end of the log you will see a bunch of calls that failed. Go back in the log to find the first one, this is often the problem. Also the problem might be right before this, so something might fail and you don’t see why, but chances are it might be due to a successful read just before it returning something it doesn’t like.

If the first error is a Read failed, then chances are you have a data integrity problem. See what View was being read and what the key is. Is this from a setup function? Is it some value that was recently deleted? Often fixing the problem just involves re-inserting this setup record. Often when you are phasing something out you might think it’s no longer needed, but somewhere in the system is an old unprocessed document that will fail as a result. Usually the regular error reporting for these sorts of problems is all you need, other times they are quite cryptic and that sends you to the log files.

If you see a lot of Read failures for no apparent reason, it might be the indexes in your database are corrupted. This is more frequent in Pervasive, but we have seen it in SQL Server also. The remedy here is to re-index the database either via a database vendor supplied tool or by doing a Database Dump and Load with the Accpac utilities. Sometimes Inserts failing for cryptic reasons can be due to this also.

If you get an Insert failing because of record already exists, don’t just delete it. This often means an options record which contains a next sequence number needs updating to a higher value. Sometimes you can do this in the application, sometimes you need to update it in the database manager.

Summary

Hopefully you won’t have to go spelunking in the log files too often. But hopefully with a bit of knowledge you can diagnose a few more problems independently without needing to call Customer Support.

Written by smist08

March 12, 2011 at 5:59 pm

Posted in sage 300

Tagged with , , ,

Diagnosing Problems with Sage ERP Accpac 6.0A

with 24 comments

Now that people are installing and deploying Accpac 6, hopefully, to get the new Web technologies working you just need to install, play with Database Setup and then away you go with no problems. Or at least hopefully this is how it works out for most people. But for those that it doesn’t, this blog posting is to help you figure out what has gone wrong. The main purpose of this blog posting is to help you track down and solve unforeseen problems on your own, hopefully giving you tools that will give you clues to what is wrong and allow you to come up with solutions.

This blog posting assumes that you can run both regular Database Setup and the classic Accpac Desktop. If you are having trouble at this step then check out these blog postings on diagnosing problems in Accpac part 1, part 2, part 3 and part 4.

Database Setup

The first time you hit some of the new technologies is when you configure the Portal’s database from Database Setup’s Portal… button. When you click here it runs a Java program that configures the portal for JDBC and tests the connection. Often if you are going to have trouble with the Java runtime or with accessing the database server from Java, this is where you will run into problems.

Bad Java Runtime

The Accpac 6 installation installs the Java Runtime version 6 update 17. If you previously have a later version of the Java runtime or let the Java Updater run then you will have a newer version. If you get an error about having trouble with a Java class such as com.sage.orion.connection.services.CommandLine, then chances are you have a problem with the runtime. We’ve seen systems where many versions of the Java runtime are installed and even though they are supposed to co-exist, there appear to be problems, plus now and then a bad Java update gets installed from the Java Updater. This was especially a problem shortly after Oracle bought Sun, at which point Oracle renamed quite a few things from Sun to Oracle and put out a couple of bad updates. They seem to be getting better at this again, but this is something to watch out for. A good remedy for this is to un-install all versions of the Java runtime and delete the program files\java directories (or the x86 variant if running 64 bit). Then either re-install Accpac or install a good known version of Java directly. Remember that Accpac is a 32 bit application and as such we require the 32 bit version of Java.

Troubles Connecting to SQL Server from Java

If your Java runtime is ok, then the next thing is: the Java classes will test your settings by connecting to the Portal database. If you get an error about “can’t locate server or user id/password are incorrect”, it might be a SQL Server configuration problem. The configuration of SQL Server from Database Setup for normal Accpac companies is fairly forgiving; it is quite smart about finding SQL Server’s and SQL Server instances and can use any communications protocol that is configured. But we access the Portal database using JDBC which is a bit more stringent. If you can setup regular Accpac companies, but not the Portal database then it is usually due to a few SQL Server configuration properties:

  1. In the SQL Server Configuration Manager – SQL Server Network Configuration – Protocols for serverinstance: make sure TCP/IP is enabled (usually it isn’t for SQL Server express and a few other varieties). Make note of the Port number used to ensure you are using the correct one (right click on TCP/IP and choose properties).
  2. Use the servername alone and the correct port number. Do NOT use server/instancename, this isn’t currently recognized (we will probably fix this for Product Update 1).
  3. In SQL Server Management Studio: in the properties for the server, security tab, ensure “SQL Server and Windows authentication mode” is selected for Server authentication.
  4. In SQL Server Management Studio: in the properties for the server, connections tab, make sure “Allow remote connections to this server” is checked.

If none of this works, then you are going to have to run a SQL Trace to see what is failing.

Hopefully this helps you get past Database Setup.

Tracing Web Requests

Below is a deployment diagram of the new Web components included with Sage ERP Accpac 6.0A. For more details look here.

As you can see requests come from the Internet and enter the Web Server via Microsoft Internet Information Services (IIS). Static content like HTML files and bitmap images will be served up directly from here. Requests for data are sent through the Jakarta ISAPI re-director to Tomcat which runs our SDataServlet which in turn calls the Accpac business logic objects (Views) to get the actual accounting data.

The general approach to troubleshooting here is to use logging to figure out how far requests from the Browser are getting and then to see why are failing at that point. All the components in the above deployment diagram have logging capabilities. Usually by default they only log errors which at this point are often all you need, however they can all be configured to provide much more detail on everything that is going on in the system.

Logging

First I’ll quickly summarize how to turn up and control the logging levels for the various services.  When running normally you won’t want to leave all these logging levels high since it wastes a lot of disk space and causes a performance drag writing out all this information. Usually you want to leave IIS logging on for every request so you can audit and keep an eye out for malicious attacks. But the other logs may as well be left at the error level.

IIS’s logging is controlled from the IIS Manager via the Logging icon for the web server. By default it’s enabled and the logs are stored in: C:\Inetpub\logs\LogFiles\W3SVC1. Note that the various versions of IIS are different so you might need to look around a bit for the settings.

The Jakarta IIS redirector is configured via the file: C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\Jakarta\isapi_redirect.properties. Change the log_level from error to debug. Note that this setting causes an extremely large amount of output and will affect server performance, so remember to set this back to error when you are done. The log file is AccpacRedirector.log in the same C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\Jakarta folder.

For Tomcat and SDataServlet, the log files are stored in: C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\logs . To configure the logging level edit: C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\webapps\SDataServlet\WEB-INF\classes\log4j.properties and change “log4j.rootLogger=ERROR, logfile” to “log4j.rootLogger=ALL, logfile”.

Diagnosing Problems

The general approach is to follow messages through the system by looking at the log files. Having a set of log files from a working system can help greatly since then you can compare them, since differences often give a clue to the cause of problems.

IIS

First, do the Browser requests even get to IIS? If not try using the server’s IP address instead of the URL. Try Browsing from the server using localhost. This can give an indication if IIS just isn’t working at all or you have DNS problems or there is some problem resolving your URL. Obvious things to check are that the IIS service is running, check the Windows Event Log if it won’t start or that IIS is configured on port 80 (HTTP) and 443 (HTTPS). Note that only one program can use a given port so if something else is using port 80, say Apache as a web server for something else then only one of them can have port 80 and one process will fail to start. If you have to use a different port, this is ok, just remember to put :portnumber after the servername in your URLs.

Next, what if the browser requests get to IIS but are rejected? What if you get a 403 error return (access forbidden)? This also manifests itself in that you will see “isapi_redirect.dll” as part of URLs in the log file. This means you don’t have sufficient permissions to execute the Jakarta redirector or there is some other sort of access problem. If you get general 403 errors on requests for static content like HTML files or image files, then the file permissions on the WebUIs folder under the Accpac directory are too strong. This folder only contains HTML, JavaScript and image files. Neither data, nor anything valuable is stored in this folder, so it is ok to relax the permissions and grant read-only access for everyone (don’t grant write since you certainly don’t want to risk a hacker writing files here). The other case is if you see isap_redirect.dll in the URL, this means you don’t have sufficient permissions to load this DLL. We install IIS with all our processes running as the local system account, this account should have permission to do anything on the machine, but it won’t have access to network resources so the most common cause of this is having key files installed on a network share. The best solution here is to ensure the Accpac programs are installed on the web server so it only needs to access the LAN to get to the database server. You can also change the user the Sage Accpac Application pool runs under to something with domain privileges.

Another problem we’ve seen with IIS is that in some cases the Accpac install fails to create the various virtual directories and application pools inside IIS. We haven’t figured out why this happens yet, but it’s worth quickly checking that the SageERPAccpac and SDataServlet applications are added to the default web site and that Sage ERP Accpac App Pool is added to the application pools. Right now the only solution we have for this is to create these entries manually, copying from a good system. I’ll update this blog posting with more details once we figure this out. It’s strange because we don’t get any errors back from IIS when this happens.

Jakarta

So far we haven’t seen problems with Jakarta. This is an ISAPI module that loads into IIS and redirects all the Accpac SData requests to the Tomcat Server. I would first look in Tomcat’s logs and then only come back here if nothing is appearing in the Tomcat logs.

Generally here you need to turn on the logging and then look to ensure messages are making it to Jakarta (i.e. the log isn’t empty). Then usually Jakarta provides fairly good diagnostics in its log when things don’t work. These are usually along the lines that the Tomcat service isn’t running, there is a port conflict or something of this nature.

Tomcat/SDataServlet

Apache Tomcat is a Java application server that runs the server side of Java web applications. The Accpac SData server is written in Java and runs under this. Tomcat’s own log files give good information if there are problems with Tomcat. Tomcat can have problems if a bad version of the Java runtime is installed, hopefully this was resolved back when you ran Database Setup, but if you get a bunch of weird Java errors in the Tomcat logs then check out the info in the Database Setup section above on cleaning up the Java runtimes.

Tomcat is also the point where we need to load DLLs from the Accpac\runtime directory. If you see errors about loading Java classes and you see a4wapiShim.dll mentioned, then you probably don’t have the Accpac\runtime folder in your system PATH. Note that since Tomcat runs as a system service under Windows, this must be in the system PATH and not your user PATH.

Next when you look in the SDataServlet log files you will see various Accpac error messages. Make sure you can login to the standard Windows desktop and access the company you are using. You can’t perform data activation from the web portal, so this needs to be done from the Windows desktop.

If you installed an alpha, beta or release candidate of Accpac 6, try uninstalling, deleting the C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6 folder and then re-installing. There might be some old leftover files that need deleting.

Watch out for IE6

We see people trying to use IE6 a surprising amount. It’s always surprising to me that people are still using IE 6, but they are. If the reported symptom is that the browser is hanging, then it is probably IE6. We don’t support IE6 and IE6 definitely doesn’t work in anyway. Worse the IE6 JavaScript engine can completely lock up Windows causing you to need to re-boot your workstation. The usual symptom of IE6 is that the first time you hit the portal it creates the uiContent table and then locks up, not creating the other three tables in the PORTAL database.

Remember we only officially support IE 7 and 8 for Sage ERP Accpac 6.0A. We will add support for Safari, Chrome and Firefox in Sage ERP Accpac 6.1A. However we won’t ever be adding support for IE 6. We will validate IE 9 once it’s released. Also if you are running IE 7, although it works, why not upgrade to IE 8? It does work quite a bit better.

Quotes to Orders

Diagnosing problems with Quotes to Orders is done the same as above. But just a couple of notes:

  • Ensure you use .Net remoting as the protocol from the Web Deployment Manager.
  • Make sure that you have activated and setup the SageCRM (EW) module within Accpac.

Fiddler

Fiddler is a great tool for tracking down problems. It spies on TCP/IP traffic through IE. Note that you have to use the proper server name in the URL, you can’t use localhost. This will show you all the requests and responses made from the Browser, often giving important clues when solving problems.

Summary

I hope as you deploy Sage ERP Accpac 6.0A, that you don’t need to refer to this blog posting very often. Further I hope that this blog posting doesn’t need to be read much. But if you do run into problems, I hope you find this information helpful.

Now off to TPAC.

Update 2011/03/29: If you have an individual workstation that is having trouble loading the Portal, try clearing the Browser cache. Sometimes if the Portal failed to load half way through then IE caches that and won’t finish until the cache is cleared and it can start clean.

Update 2014/10/02: For SQL Server Express, by default it uses dynamic ports. To work with the Portal you need to configure it to use a specific port. To do this run the SQL Server Configuration Manager. Choose “SQL Server Network Configuration”. First ensure TCP/IP is enabled, then right click on it and choose properties. Choose the IP Addresses tab, scroll to the bottom and in the IPAall setting, blank out the TCP Dynamic Ports entry and set the TCP Port to 1433 (or whatever you need).

Written by smist08

March 5, 2011 at 5:24 pm

Posted in sage 300

Tagged with , , , ,