Crossing the (file size) line in MYOB

Ever wondered what the maximum file size for a MYOB data file is?

Want to guess? I’m pretty sure it’s somewhere around 2.0Gb myself. Why? Because I have just spent many, many hours optimising a (.MYO) data file to get it back under this magic limit. I can’t say for sure, but if you get a MYOB file to 1,906,528KB you might also run in to this issue.

In fact, please email me if you do, because I would like to have confirmation that it’s not just this particular installation. Here is my “experience”…

The night before: Run the invoice import which uses the ODBC link to insert ~4000 invoices that were generated earlier in the day. These are the monthly invoices for the company. Yes, I have told them (for years) to get a different accounting package.

~8:30am: I get the call that the MYOB file is “not working”. I try to open the file and receive an “Error 1233: -3000 in DMBeginTransaction” message. After a bit of looking around the only posted solution is to optimise the file and hope for the best.

12:03pm: Optimisation has finished and the file has been shrunk to 1,895,424KB. It will open in MYOB again.

~4:00pm: Someone notices that a bunch of recently imported invoices so I kick off the various comparison processes that marks invoices previously thought to have been imported, that are no longer present in the MYOB file, as un-imported. This then kicks off the import process, which starts importing the outstanding invoices.

4:24pm: Auto-import process crashes. All users in that MYOB file also crash.

~4:27pm: We discover the backup process has silently failed two days ago and that the file that was backed up last night was 1,906,528KB in size. There is no backup. The decision to scrap the days transactions is made and I restore the recovered file from 12:03pm.

~4:30pm: I kick of the roll-over process, effectively removing all transactions from the previous financial year. A process that someone “just never got around to doing it”.

7:05pm: Roll-over process completes. File is now EVEN LARGER @ 2,137,120KB. It won’t open – “Error 1233: -3000 in DMBeginTransaction”.

7:12pm: I apprehensively kick off the file optimisation process. It seems to be working.

~8:13pm: Optimisation finished. File is now 1,018,400KB. This might actually work! Missing the invoices still, but now I can re-import them without going over any soft limit.

8:55pm: The import has finished without issue and the MYOB file seems to be fine. Huzzah!

Now the Accounts Department only has to figure out what other transactions from the last couple of days have been wiped out and then they can continue with their week.

NOTE: When optimising huge MYOB files, the optimiser doesn’t seem to be able to calculate the required/free space on the disk correctly. I have been doing all the optimisations on a 50GB drive with upwards of 40GB free space. Each time I have started to optimise a new file (I am using copies, it’s a long process) the optimiser tells me that there is not enough free space. I copy and paste the file back into its folder (making a duplicate with ” – Copy” in the name) and then, suddently, the optimiser will let me process the file. I do not have an explanation for this, but I have noticed this issue for at least the last 3 years.

Forensic auditing with triggers

Something weird was happening – Transactions were disappearing; after a while – Specific transactions.

So we needed to find out, undeniably, who was doing it without alerting them that we knew it was happening. When you discover that data has been obliterated, and have no way to track what has happened, the first thing you need to do is start (silent) auditing and then you have to wait. You have to let them continue to do it, so you have some idea of how long they might have been doing it before they were discovered.

It’s running on SQL Server so there isn’t too much of an issue to create a silent audit trail. First create a table for the auditing entries to go in to which, importantly, contains extra fields: altered_at and altered_by.

CREATE TABLE [dbo].[bookings_audit_trail](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [booking_id] [int] NOT NULL,
...
    [created_by] [varchar](40) NULL,
    [created_at] [datetime] NULL,
    [altered_by] [varchar](40) NOT NULL CONSTRAINT [DF_bookings_audit_trail_created_by]  DEFAULT (suser_sname()),
    [altered_at] [datetime] NOT NULL CONSTRAINT [DF_bookings_audit_trail_created_at]  DEFAULT (getdate()),
 CONSTRAINT [PK_bookings_audit_trail] PRIMARY KEY NONCLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Then create a trigger on the working table that fills the audit table when records are deleted. I have set it to ignore the Administrator account because I know that the system uses that account for clean up of the table (and because I know the Administrator account cannot log in interactively to this system)

CREATE TRIGGER [dbo].[bookings_deleted]
   ON  [dbo].[bookings]
   AFTER DELETE
AS
BEGIN

    SET NOCOUNT ON;
    IF (system_user != 'Administrator')
    BEGIN
        INSERT INTO
            bookings_audit_trail
            (
                booking_id,
                site_id,
...
                created_by,
                created_at
            )
        SELECT
            id,
            site_id,
...
            created_by,
            created_at
        FROM
            deleted
    END

END

And now the waiting begins…

Report Server is not emailing reports

Failure sending mail: The transport lost its connection to the server.

That’s the error message that has been plaguing me (and the support contractor) for two months. Not terribly useful and, according to the internet, there are many, many ways to resolve it. None of which worked in this case.

I found the solution while looking at something else: my laptop was awfully slow to load web sites, on the first load. Subsequent loads were fine, but every time a new domain was resolved, it took a VERY long time to find the IP. So, after a few nslookup requests on the DNS server machine, it became obvious that the DNS forwarders had become rubbish. 8+ seconds to resolve a domain name.

Solution: Change the DNS forwarder order to use the fastest upstream DNS servers and now we have fast name resolution.

This then resolved the Report Server connection issues (why this would affect sending mail internally, I do not know, but it did).

Congratulations! You have under-exceeded!

Mediocrity is no longer acceptable in business, but how do you tell the client that they have cleared the lowest bar?

Perhaps a more direct approach is needed!

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.

MYOB Account Sync

What needs to be done?

Update the booking system(s) with the latest payment information from MYOB.

Why do we do it?

The company does not want to service customers that are not paying their account.

Who is responsible?

• Automated Task Scheduler process on IWM-TS-001.

When

• Every hour, on the hour, between 6:00am and 6:00pm
• Booking systems that are live: [Instant, Bunbury, Northwest, Ace & Mandurah]

How is it done?

1. Import MYOB data via MYOB ODBC link (myob_sync.accde)
   1. Import MYOB Items details into booking system
   2. Import MYOB Customers details into booking system
   3. Import MYOB Sales information into booking system
2. Execute processing routines on the SQL Server
   1. Compare product codes
      1. Match product_options.myob_code => imported_items.Item_Name
      2. Email ([settings].[sync_notify_email]) the list of item codes that are missing from MYOB
   2. Compare Customer information
      1. Match customers.name => imported_customers.company_name
      2. Email ([settings].[sync_notify_email]) the list of Customers missing from MYOB
      3. Email ([settings].[sync_notify_email]) the list of Customers with different addressing details
   3. Update payment information
      1. 1. Copy details from MYOB_Sales –> imported_invoices table
         2. convert all the numeric invoice numbers to long type
         3. Remove records that didn’t covert to long
         4. Match imported_invoices with existing invoices and save their details to tblToBePaid
      2. Clear all agreed payment dates that are in the past (the agreement has expired)
      3. Update account status for all customers
         1. Customers who are over due but not on hold => put them on hold
         2. Customers who are on hold but no longer overdue => take them off hold
         3. Add a message to any client that has been switched saying why it has been switched (Account Paid, Automatic hold)
      4. Set any held customers with “agreed payment dates” to off hold (they have agreed to pay us, but the date has not yet passed so we should not break our side of the bargain)

Important Notes

• This system will operate well as long as the Account Notifications procedures are followed by staff.
• Occasionally the myob_sync.accde file is renamed Database.mdb for some (unknown) reason. This stops the scheduler task from running correctly, so I have added a MYOB Sync Fix step to the schedule, which should resolve the issue for the moment.

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.

Need a database ‘fixed’?

I have extensive experience in developing Access databases.

I have worked with Access 2007, 2003, xp, 2000, 97 and even 2.0!

I also have a solid understanding of SQL Server 2005/2000 and have built/maintained/improved upon many enterprise level systems.

openssl, ruby, rails, mysql and SHA256 errors

In the interests of not having to re-figure out how to install the above systems on my mac os (tiger 10.5.8) I am going to post the commands that I eventually used to get the damn thing to, essentially, recognise the new version of openssl I installed to be able to use the SHA256 digest method so I could talk to facebook correctly.

3.days.ago everything went pear shaped.

Prior to running this:

• Install http://mxcl.github.com/homebrew/“>homebrew (to make installing openssl “easier” but really could be replaced next time with a curl/tar/configure/make set).
• Make sure your $PATH lists /usr/local/bin before /usr/bin, as outlined on http://hivelogic.com/articles/ruby-rails-leopard“>hivelogic (most of these commands come from there).
• Slap yourself once (again) for forgetting, 4.months.ago, to turn your Time Machine back on.


brew install openssl
mkdir /usr/local/src
cd /usr/local/src
curl -O ftp://ftp.ruby-lang.org/pub/ruby/1.8/ruby-1.8.7-p330.tar.gz
tar xzvf ruby-1.8.7-p330.tar.gz
cd ruby-1.8.7-p330
./configure --enable-shared --enable-pthread CFLAGS=-D_XOPEN_SOURCE=1 --with-openssl-dir=/usr/local/Cellar/openssl/0.9.8o/
make
sudo make install
cd ..
curl -O http://rubyforge.iasi.roedu.net/files/rubygems/rubygems-1.3.4.tgz
tar xzvf rubygems-1.3.4.tgz
cd rubygems-1.3.4
sudo /usr/local/bin/ruby setup.rb
cd ..
sudo gem install rails
sudo gem install mongrel
sudo gem install capistrano
sudo gem install mysql -- --with-mysql-dir=/usr/local/mysql

And that should just about do it. Check it’s all working (specifically the SHA256 part) with this command

ruby -ropenssl -e 'p OpenSSL::Digest::Digest.new("sha256")'

which should not return any errors. That being the point of the entire exercise.

Simple Wiki (UseMod)

I’ve been using a wiki for documentation purposes (indeed!) at a client site in the last few months. It’s fantastic! Once again, usefulness trumps technology. The wiki (http://www.usemod.com/cgi-bin/wiki.pl” target=”_blank”>UseMOD) is a single perl script that happily runs on the Windows, Apache, SQL Server, Cygwin Perl/Rails/PHP (WASC, I guess) machine I have built to run their web sites. Very simple to configure and pretty easy to understand if you need to make changes, which I did, of course.

Getting the localtime right! Pretty simple stuff, but it makes such a difference when looking at the historical changes page. The changed lines are in red.

sub CalcDay {
  my ($ts) = @_;

  <span style="color: #ff0000;">#$ts += $TimeZoneOffset;</span>
  my ($sec, $min, $hour, $mday, $mon, $year) = localtime($ts);
  if ($NumberDates) {
    return ($year + 1900) . '-' . ($mon+1) . '-' . $mday;
  }
  return ("January", "February", "March", "April", "May", "June",
          "July", "August", "September", "October", "November",
          "December")[$mon]. " " . $mday . ", " . ($year+1900);
}

sub CalcTime {
  my ($ts) = @_;
  my ($ampm, $mytz);

  <span style="color: #ff0000;">#$ts += $TimeZoneOffset;</span>
  my ($sec, $min, $hour, $mday, $mon, $year) = localtime($ts);
  $mytz = "";
  if (($TimeZoneOffset == 0) && ($ScriptTZ ne "")) {
    $mytz = " " . $ScriptTZ;
  }
  $ampm = "";
  if ($UseAmPm) {
    $ampm = " am";
    if ($hour > 11) {
      $ampm = " pm";
      $hour = $hour - 12;
    }
    $hour = 12   if ($hour == 0);
  }
  $min = "0" . $min   if ($min<10);
  return $hour . ":" . $min . $ampm . $mytz;
}

sub TimeToText {
  my ($t) = @_;

  <span style="color: #ff0000;">$t-= $TimeZoneOffset;</span>
  return &CalcDay($t) . " " . &CalcTime($t);
}