Connect to server databases with SQL-DMO

from the Artful SQL Server & Access Tips List

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
End Function

The 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
End Function

I 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.

Return to the Artful SQL Server & Access Tips page