Linglom's blog

Problem

If you execute a vb script which use SQL-DMO on SQL Server 2008, you will notice that the script doesn’t work at all.

You will see the error message as similar to this one when you execute the vb script:

Error: ActiveX component can’t create object: ‘SQLDMO.SQLServer’
Code: 800A01AD
Source: Microsoft VBScript runtime error

Cause

This is because SQLDMO is deprecated since SQL Server 2005. But SQLDMO is installed on SQL Server 2005 by default so you can run a vb script which uses SQLDMO without any error on SQL Server 2005. On SQL Server 2008, SQLDMO is not installed by default . If you want to use SQLDMO, you have to install it manually.

Solution

To use SQL-DMO on SQL Server 2008, you need to download and install Microsoft SQL Server 2005 Backward Compatibility Components to solve the problem. You can go to Microsoft Download Center and search for “Microsoft SQL Server 2008 Feature Pack” and sort by Release Date to find the latest version. Or you can download at Linglom’s Download Page.

Step-by-step

1. Download Microsoft SQL Server 2005 Backward Compatibility Components. Next, execute the setup file.
2. On Welcome to the Install Wizard for Microsoft SQL Server 2005 Backward compatibility Setup, click Next.
   
3. On License Agreement, select ‘I accept the terms in the license agreement‘. Then, click Next.
   
4. On Registration Information, enter your personal information. Then, click Next.
   
5. On Feature Selection, ensure that SQL Distributed Management Objects is selected. Then, click Next.
   Note: SQL-DMO is the feature that is require to run a vb script that uses DMO.
   
6. On Ready to Install the Program, click Install.
   
7. Installing Microsoft SQL Server 2005 Backward compatibility.
   
8. On Completing the Microsoft SQL Server 2005 Backward compatibility Setup, click Finish. Now try to run vb script again, the problem should be gone!
   

Reference

Where the Heck is DMO?