SQLDMO for SQL Server 2005/2008 and Your Installation

This article is for those building an install for a VB6 program that uses SQLDMO to control Microsoft SQL Server. Connecting to SQL Server 2000 is easy but now you are having trouble dealing with SQL Server 2005 and 2008.

Here is the solution.

---

Microsoft provides a backward compatible (BC) version of SQLDMO for 2005/2008 but it is only available as a full install (SQLServer2005_BC.msi). Adding the individual files to your install requires a lot of sleuthing. Adding SQLServer2005_BC.msi as a prerequisite only makes sense if it is required under all scenarios and launching it during your install is not clean and has its own problems. Wouldn’t it be nice if Microsoft had simply provided a merge module for SQLDMO BC?

A small piece of a very large application that I wrote uses SQLDMO and it is not worth it to me to update this code to use .Net and SMO. Since the files and settings required to add SQLDMO BC to my install should be treated as a single unit, I chose to build my own merge module rather than add the files and settings directly. The side benefit of building a merge module is that now I can distribute it to you.

I first made this merge module available in 2007 with an entry at the following newsgroup thread: microsoft.public.sqlserver.tools/topic65092. I still get regular requests for this and I am still using it in the latest version of our app.

You can download this 2M zip here.

I have tested the following scenarios:

1. Nothing installed
   
2. DMO 8.00.7600 (SQL 2000 tools)
   
3. DMO 8.05.1704 (SQLServer2005_BC.msi - this version)
   Add does not replace files but adds 1 to the usage count, remove reduces the usage count instead of removing the files.
   
4. DMO 8.05.2004 (SQL 2005 tools)
   Add does not replace files, remove does not remove files

I have tested on the following OSs:

1. Windows 2000
   (scenario 2 and 3 - uninstall didn't remove files, but same thing happens with SQLServer2005_BC.msi. This probably is an issue with the version of Windows Installer on Windows 2000.)
   
2. Windows 2003
   ((scenarios 1,2, and 3)
   
3. Windows XP
   ((scenario 4)
   
4. Windows Vista
   ((scenario 1 and 3)
   

If you download this file, please add a comment below. Perhaps include your company name and the name or description of the product that will be using this.