MYOB ODBC Error #7495

Unexpected internal c-tree(R) error #7495 (uerr_cod=0)

That’s the error message I was getting one day. Nothing major happened, the update routines just stopped working on day!

I’m importing a bunch of data from the usual tables (CustomLists, Customers, Items, Sales) in to the SQL Server back end for faster processing against the company’s CMS. I do this because of the way MYOB has done it’s ODBC driver, every time you do a query, all the table records are read in to memory and THEN the filters are applied, it’s horrendous. Especially if, like this company, you have upwards of 100,000 sales lines. So anyway, it happily dumps the data into the SQL Server tables (using MS Access 2007 as an intermediary) without issue.

The error occurs when I start to insert new records in to the MYOB file.

I’ve tried using the same connection (Reading from a RW connection). I’ve tried using a two connections (Reading from a RO connection and writing to a RW connection); to no avail.

The solution?

ONLY read from or write to the MYOB file while the connection is open. I have taken this to the extreme and put a switch in at the start of the run; if it’s 4am – write any new sales to the MYOB file; if not – read all the MYOB details and update the CMS accordingly.

The error is gone and the work flow is easy to understand for the staff:

• Each morning new invoices should be in MYOB and;
• During work hours the payment details entered in MYOB will be reflected in the CMS every hour.

Obviously the memory handler for the underlying database couldn’t deal with the large number of rows being returned.

Copy database diagram(s) in SQL Server

Occasionally, very occasionally, I find myself having one database diagram to rule them all. This is the one that I have spent the most time on; the one that unifies the most tables and has the simplest layout. Something clicked when I was filling in the connections and now I want to bring it to all the other versions of the database, but there’s no cut-and-paste!

After a little searching I found this:

INSERT INTO
    target_db.dbo.sysdiagrams
SELECT
    [name],[principal_id],[version],[definition]
FROM
    source_db.dbo.sysdiagrams

Just change the target_db and source_db labels and it will bring everything across. Obviously there are further refinements that could be put in the query, but the basics are there, extrapolate as required.

Online Job Cards

The system is designed to improve response times when operations are flowing smoothly so that issues can be resolved faster when they occur. Drivers are able to view and update their run sheet as soon as the job is done using their data enabled mobile devices. Jobs are displayed in the order of importance and can be marked as having been done or having issues that need to be resolved.

The Online Job Cards system is integrated directly into the existing legacy booking system, providing a fast reliable link between the drivers performing the services and the operations staff overseeing each division. General job status is provided visually using colour coding on the operations mangement screens in real time. When a new job is allocated to a driver, it will appear on their mobile device as soon as they refresh their Online Job Card.

Another excellent situation for data enabled mobile devices. Further updates are planned, including obtaining customer proof-of-completion signatures for invoicing purposes and using Location Services to pinpoint where the jobs are being performed.

This solution uses:

• Microsoft SQL Server
• Ruby on Rails
• Telstra NextG
• HTML4+ enabled web browser (iPhone/Android/iPad)

Recruitment Database

Global Health Source aims to provide their clients with a short list of high quality candidates for review, ideally within a few days. Candidate details, such as primary professions, skills and their proficiencies, accreditations, and visa history are all recorded and instantly searchable. Client details, including candidates presented for interview and their outcomes are also recorded to get a complete picture of their success rates over time.

Supplying allied health professionals to locations in Australia and New Zealand. The database tracks members from all over the world (Australia, Ireland, New Zealand, South Africa and the United Kingdom to begin with); their current qualifications; future plans; visa arrangements; and local accreditation progress. Placement officers are constantly in contact with candidates and clients throughout the day so data entry must remain a simple process. The search form allows for very detailed criteria so that the least number of enquiries can be made while finding candidates who are available, and interested, in the position.

This system has recently been migrated from an Access 2007 back end over to SQL Server 2008, the process was done in-situ over a period of weeks (and while it was being used in a live environment!). A more detailed search function is being investigated, along with increasing the list of details being stored for clients and candidates.

Technology used

• Microsoft SQL Server
• Microsoft Access 2007

Better business through Report Server

I have been having a fantasic time creating reports for Microsoft’s Report Server. It’s actually been very usefull for the company to be able to have PDF versions of various regular reports being automatically emailed to the people who are supposed to be reading them.

The upshot of this automation is that the reports that haven’t been well thought out are quickly brought in to focus and revised by the people who use them. The number of reports that are being sent out has dropped and the information contained in the remainder are salient and accurate.

This translates into fewer people reading shorter reports which allows more time for smart decisions to be made within the company.

Online ordering of waste services

Brief

The client wants to be able to offer members of the public the ability to order and pay for bin services via the internet (without applying for an account). The process must be very simple and understandable because no human interaction will take place prior to the order being placed. The system must ensure that; the correct pricing is used; the service address is located within a servicable area; the customer’s placement instructions are not overly complex; and that the payment has been received prior to accepting the booking.

The client would also like to offer Account Customers (“Members”) the ability to order and cancel services via the internet. Members can log in to view and update their outstanding service orders. The Member’s area will show all the current locations and the various services we provide at each one. Any complex enquiries will be handled through the call centre.

The solution must keep in line with the existing web site’s look and feel. The solution needs to integrate seamlessly with the existing (proprietory) business database system and its business rules.

Solution

A step-by-step wizard was created for the creditcard (public) ordering function. The steps are designed to flow logically from one to the next while obtaining the required information quickly so that each subsequent step is as concise at possible. The information funnel looks like this: Location > Product > Payment > Confirmation > Receipt.

A Member’s area was also created, using standard authentication techniques, that provides a multi-tiered drill down navigation system. The top level provides a listing of account details coupled with the (paginated) list of active sites. Clicking on any site will display more details such as any outstanding orders for bin services and any active contractual arrangements. Members can order new services using a wizard similar to the public one, except that the location and payment steps are not necessary. Members can also cancel any services that meet the cancellation requirements.

This solution uses:

• Microsoft SQL Server
• Ruby on Rails

A cursur-y look at SQL

I have been rewriting the invoice generating sub-system for the company today. I’m doing it in two steps; a direct port of the (VB6) code that currently runs and then I’ll see if it’s worth adding some SQL trickery to optimise the process. Step two probably won’t happen.

The ported process is hundreds of times quicker to run than the existing code. The original code takes about 10 minutes to complete – SQL Server takes 3-4 seconds. The original code has progress bars and other “this is going to take a long time” entertainment but if it’s only going to take less than 5 seconds to get the same results, I won’t be coding them across. They were only needed because it was taking such a long to complete.

I have used cursors to get through the iterative process of decoding what to do with each item that is ready to be invoiced. I know it’s frowned upon by “them that know” but quite simply, it is the most cost effective solution for the moment – there aren’t millions of invoice lines needing to be processed so the extra work required to “make it perfect” isn’t justified.

An itchy trigger finger

Aren’t triggers just marvellous things? An hour after I had added a simple update trigger to the most active table in the database there were over 220 records in the audit table. With this kind of information I could easily create employee activity demographics that can be used to review resource allocation.

There seems to have been very little CPU load put on to the (DC) machine running the SQL Server. Not much of a surprise there as the system isn’t particularly complex at the moment and most data has a short active time frame after which it is rarely accessed. Future developments will see that change slightly, but even then the system won’t be doing hundreds of thousands of transactions an hour.

Management is quite excited with the increased auditing capabilities and is starting to see palpable benefits to having made the shift to SQL Server. All I have to start doing now is managing their expectations so this excitement can be turned into future expansion contracts.

A form with a view

The full data conversion from MS Access to MS SQL Server last Friday night has been very successful! Not only was there no data loss, but very few changes were required to the interface system to keep it working through the weekend.

Now the final chapter of the full conversion begins: creating views for all the long process queries that used to take minutes to return results.

I have already begun the process, starting with the most troublesome queries that are regularly used. The idea being that the most user time will be saved by optimising them first. The pay off is huge for a small amount of work. As the list to be converted shortens management can decide if they want to continue with the process.

I will also be using triggers to add some proper auditing of various tables (those related directly to cash value transactions mostly) which is sewhat of a worry as the company is going to be able to see just how often records are changed by their staff. All in the line of performing their jobs, of course, but through full auditing analysis of the “surface area” of the various systems can be done and I can begin to reduce it to as small a size as possible.

SQL Server conversion

I’ve been having great success with converting some of my split (front-end/back-end) MS Access 2007 databases to an SQL Server back-end.

The upgrade wizard was only so helpful, I had to turn off all the extra options to get the many years of data into the SQL Server. It has become clear to me that over time the data has had various levels of ‘goodness’ and the integrity rules have been quite open to interpretation, or sometimes just automatically turned off when the situation demands it (compact database function).

What the conversion process has allowed me to do is revisit all the data constraints for all the tables and make sure they correspond to the new system’s requirements. I have had to replace quite a few null values, but the overall effect has been to really focus in on how the system has been used in the past. In spite of the way it was designed to be used.

I am also looking forward to having a much less invasive auditing mechanism through the use of triggers. The old method relied heavily on calling the right sub-routines at the interface level which is not the most ideal solution, but certainly provided a level of functionalty that worked.