Data Management

Connect to a server and its databases using SQL-DMO

Microsoft provides an object-oriented library for dealing with SQL objects: SQL-DMO (SQL Distributed Management Objects). This article shows you the basic steps on how to connect to a server and its databases using SQL-DMO.

Microsoft provides an object-oriented library for dealing with SQL objects: SQL-DMO (SQL Distributed Management Objects). By using this library, you can 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 Function

The next (lower) element in the 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 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.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Editor's Picks

Free Newsletters, In your Inbox