How to eliminate EXCEPTION_ACCESS_VIOLATION errors when using SQL-DMO with VB

This is another one of those things I wish Google had told me so I wouldn’t have had to figure it out myself. Unless you were specifically looking for this information, it’ll probably be completely useless to you, so by all means skip this post and go read something more interesting.

Over the last few days, I’ve been implementing a tool at work using VB6 that uses Microsoft’s SQL-DMO component to build a nice database installer and upgrader for the software I’m writing. Before upgrading a database, I use SQL-DMO’s Backup object to create a backup of it. Since these databases, in a production environment, can get pretty huge, I also implemented an event handler for the Backup object’s PercentComplete event, which fires at a set interval and allows you to update a progress bar with the progress of the backup operation.

The problem I ran into is that, while the backup operation itself runs just fine, the Backup object throws this nasty error as soon as the operation completes:

Run-time error '-2147221499 (80040005)':

[SQL-DMO]Code execution exception:

After much fruitless perusing of documentation and Googling, I eventually stumbled upon the discovery that the errors stop occurring if you simply implement event handlers for all of the Backup object’s events, rather than only implementing the handlers you’re using. As soon as I added those empty event handlers, things started working without a hitch.

As an ironic sidenote, my Google searching turned up a report that Microsoft’s own SQL Server Enterprise Manager (which also uses the SQL-DMO component) suffers from the same problem.