The SQL Distributed Management Objects (SQL-DMO) provide a set of OLE objects that enable applications to use management features of SQL Server. This functionality helps to extend the capabilities of SQL Server by providing access to any 32-bit OLE-compliant application. This article gives an overview of SQL-DMO and provides a demonstration on how to access SQL-DMO via Active Server Pages (ASP).
What is SQL-DMO?
The SQL-DMO is a part of the Distributed Management Framework that Microsoft provides for SQL Server. This framework is three-tiered consisting of SQL Server, SQL-DMO, and SQL Executive. The SQL-DMO is the middle level that provides the interface to manage features of SQL Server. The SQL-DMO has its own hierarchy consisting of the application, SQL Server, database, and table objects. The important point to remember is that each level consists of objects related to the level. For example, the database level consists of stored procedure and table objects.
Microsoft introduced SQL-DMO in SQL Server version 6.0. A good rule to observe is to use the proper version of SQL-DMO with the proper SQL Server. These DLL's are located within the BINN directory of your SQL Server.
SQL Server 6.0
SQL Server 6.5
SQL Server 7.0 & SQL Server 2000
SQLDMO.DLL - The names are the same, but they really are different. You can not access SQL Server 2000 using the SQL 7 SQL-DMO, although you can do vice-versa.
SQLDMO.DLL - Both SQL 7 and SQL Server 2000 should work.
(Save your self some stress and just load the client tools for the given SQL Server on your web server!)
How can we use this stuff?
The text book answer would be "The SQL-DMO allows you to create custom management applications for SQL Server and MSDE".
It is really up to you on how you decide to use the SQL-DMO. I use it to modify stored procedures, execute jobs and DTS packages, and view system information for clients who have chosen to run the MSDE or SQL Server as their web server database. Since I can not visit each site every time a client has a problem, I have developed a set of ASP scripts that are loaded on their web servers that enable me to monitor and administer their databases.
I hope this article and sample code will produce a few more uses for this powerful feature.
A few words about the sample application
I have created a simple application utilizing ASP and SQL-DMO that connects to a server, gets a list of stored procedures for a given database, allows you to modify the text, and finally applies the changes to the databases.
We only touched the surface of what SQL-DMO can do. I will be happy to explore SQL-DMO in more detail if you tell the staff at www.asp101.com that you want more. If you have any questions or problems about this article or source code, my contact information is listed below.