|SQL-DMO (SQL Distributed Management Objects) is a Microsoft object-oriented library for handling SQL objects. It helps you perform a number of cool tasks that are difficult or impossible to do in T-SQL.
As with all object libraries, you have to learn to walk the hierarchy. The highest-level object in the library is the collection of servers. Below that level are the databases, and the tables, views, sprocs, and so on that reside within a given database. This article will show you the most basic steps on how to connect to a server and its databases using SQL-DMO.
The first thing you need to do is obtain the list of servers. The following code will deliver the list of servers to a listbox:
Function listServers(oControl As Object) Dim oApp As SQLDMO.Application Dim oNames As SQLDMO.NameList Set oApp = New SQLDMO.Application Set oNames = oApp.ListAvailableSQLServers() For Each oName In oNames oControl.AddItem oName Next End FunctionThe next-lower element in the object hierarchy is the list of databases. This is where you may need to supply a userid and a password (if you aren't using integrated security). Note: Be sure to read the following code before using it, particularly the demarcated lines.
Function listDatabases(vServer As String, oControl As Object) Dim oSrvr As SQLDMO.SQLServer Set oSrvr = New SQLDMO.SQLServer oSrvr.LoginSecure = True ' ---------------------------- ' use this line for integrated security oSrvr.Connect vServer ' or this for sql login oSrvr.Connect vServer, "sa", "SQLTips" - substitute your sa password ' -------------------------- For Each oDatabase In oSrvr.Databases oControl.AddItem oDatabase.Name Next End FunctionI used such code when I was deploying an application written against MSDE, which doesn't include Enterprise Manager, Query Analyzer, and all those other nice tools. With SQL-DMO, I was able to write a small application that enables users to create a new database, install the tables we shipped (mostly blank but some of them were pre-populated), and then load and go. I encourage you to investigate SQL-DMO further. By descending into the hierarchy, you can walk the list of tables, queries, and so on, using very simple code like the examples above. Once you dive in, it turns out to be very easy, and it can save you lots of time.
Last updated 22 May 2009