Introduction to SQL-DMO

By Damian Maclennen on 18 August 2002 | 10 Comments | Tags: DMO


Most SQL Server administrative tasks are programmable thanks to a set of objects known as SQL-DMO. This article introduces the concepts of programming DMO, and steps you through some basic, and commonly requested examples.

Distributed Management Objects (DMO) is a set of programmable objects that come with SQL Server that make it easy to programatically administer your databases. SQL-DMO is actually the foundation of Enterprise Manager, so you can pretty much do anything programatically that you can do in the management tools. Some of these tasks include :

  • Scripting Objects
  • Backing up databases
  • Creating jobs
  • Altering tables

    ... and much more.

    The fun happens inside sqldmo.dll which sits in the \Tools\Binn directory under your Microsoft SQL Server installation. As it is a COM object, you can use it in any COM friendly platform. This includes C++, VB, WSH, ASP, Delphi and of course .NET.

    For the purposes of this article, I am going to give examples in Visual Basic 6. These examples will work with very little modification in VB Script, including ASP.

    The first step is to connect to a database server. You can do this using SQL authentication, or use the built in NT authentication depending on your application.

    Connecting using SQL Authentication

    
    Dim objDMO as SQLServer
    Set objDMO = new SQLDMO.SQLServer
    objDMO.Connect "(local)", "sa", "of_course_I_changed_my_password"
    
    

    Connecting using NT Authentication

    This is as simple as setting the "loginsecure" property to True and leaving off the login and password.

    
    Dim objDMO as SQLServer
    Set objDMO = new SQLDMO.SQLServer
    objDMO.loginsecure = true
    objDMO.Connect "(local)"
    
    

    Don't forget at the end of this you will want to disconnect and clean up your objects with a simple :

    
    objDMO.DisConnect
    Set objDMO = nothing
    
    

    The Tables Collection

    The object model within DMO is very similar to the heirachy in Enterprise Manager. Underneath each server is a collection of databases. Each database has collections of objects. Tables, Stored Procedures and Views all exist in collections under a database.

    Here is an example of some code that would loop through the tables collection to return the name of each table.

    
    'Assuming, we have set up our connection.
    
    Dim objDB As Database
    Set objDB = objDMO.Databases("northwind")
    
    Dim oTable As Table
    For Each oTable In objDB.Tables
        MsgBox oTable.Name    
    Next
    
    

    Another common use for SQL-DMO, and certainly one that gets requested in our forums on a regular basis, is scripting database objects. Scripting is quite simple. Once we have a table object, as seen in the above example, we can call the script() method and we are returned a string that contains the Create Table script for that object. There are a number of scripting options that we can set for this method, for example we can choose not to include any constraints, or only have contstraints, etc, etc, etc. But I will let you look these up for yourself in Books Online.

    Here is some code that will generate a script for the employees table in the Northwind database.

    
    'Assuming, we have set up our connection.
    
    Dim objDB As Database
    Set objDB = objDMO.Databases("northwind")
    
    Dim oTable As Table
    Set oTable = objDB.Tables("employees")
    
    'Assuming we have a text box control named text1
    
    Text1.Text = oTable.Script()

    Now, this article would not be complete without a bit of SQL code. Knowing that we can call COM objects in our T-SQL scripts using the sp_OACreate procedure, it makes sense to put these two concepts to use. Without going into the details of how this works (click the above link for some related articles), a basic script to call DMO from T-SQL is :

    
    declare @objDMO int
    declare @objDatabase int
    declare @resultCode int
    declare @dbname varchar(200)
    declare @tablename varchar(200)
    declare @cmd varchar(300)
    declare @temp varchar(8000)
    
    Set @dbname = 'PUBS'
    Set @tablename = 'Authors'
    
    
    EXEC @resultcode = sp_OACreate 'SQLDMO.SQLServer', @objDMO OUT
    if @resultcode = 0
    print 'Created Object'
    
    Exec @resultcode = sp_OASetProperty @objDMO, 'loginsecure', 'true'
    
    
    EXEC @resultcode = sp_OAMethod @objDMO, 'Connect', NULL, '(local)'
    if @resultcode = 0
    print 'connected'
    
    Set @cmd = 'databases("' + @dbname + '").tables("' + @tablename + '").script'
    Exec @resultcode = sp_OAMethod @objDMO, @cmd , @temp OUTPUT, 4
    print @temp
    
    
    EXEC @resultcode = sp_OADestroy @objDMO
    if @resultcode = 0
    Print 'destroyed object'
    
    

    Conclusion

    This has been a very brief introduction to SQL-DMO. It is a topic that a huge book could be written on so I have barely scratched the surface. The examples here are very basic and not very useful in themselves but give you an idea as to what can be achieved. I urge you to experiment with DMO and see what you can do with it. If you have any questions, post them in the forums here and I am sure you will get some help.

    For more information on SQL-DMO, look in Books Online, and do a search on MSDN. There is heaps of good stuff there.

    Good luck with it!

  • Discuss this article: 10 Comments so far. Print this Article.

    If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

    Email Address:

    Related Articles

    Using SQL DMO and Windows Script to customize script generation (23 September 2002)

    Finding SQL Servers running on a network (15 August 2001)

    SQL-DMO Automation (microsoft.com) (12 May 2001)

    Error handling in long-running batch jobs (29 January 2001)

    Other Recent Forum Posts

    some help pls.... (6 Replies)

    Control XML Output (0 Replies)

    Create database (10 Replies)

    Getting duplicate records, don't know why (4 Replies)

    List all the tables from Stored Procedure (0 Replies)

    Concatenate Output Param (5 Replies)

    Group by with Adventureworks (1 Reply)

    Multiple Parameters Using Full Search Index (0 Replies)

    Subscribe to SQLTeam.com

    Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

    SQLTeam.com Articles via RSS

    SQLTeam.com Weblog via RSS

    - Advertisement -