| |
In my last article on Understanding
and Using SQLNS, we saw how the power of enterprise manager
user interfaces were made available to applications. In this article
we are going to see an even more powerful API called SQL-DMO
(SQL Distributed Management Objects). SQLDMO is a collection
of objects encapsulating Microsoft SQL Server database and replication
management. Using SQL-DMO, an application can tap into any SQL
Server and manipulate all its objects (for example, querying objects
and creating objects). SQL-DMO encapsulates Microsoft SQL Server
components, presenting the attributes of the component piece to
you as the properties of an object instance. You can alter the properties
of the instance, or use object methods to automate SQL Server administration.
SQL-DMO is a dual interface COM, in-process server
implemented as a dynamic-link library (DLL). When creating a SQL-DMO
application, you can use any OLE Automation controller or COM client
development platform like .NET or VB. In this article we will use
VB.NET for all examples. |
|
|
SQL-DMO System Requirements
SQL-DMO uses the Microsoft SQL Server ODBC driver to connect to
and communicate with instances of SQL Server. Stored procedures
supporting SQL-DMO are installed on each instance of SQL Server.
SQL-DMO clients require SQL Server ODBC Driver, version 3.80 or
later, which ships with SQL Server 2000. The client network library
must be properly configured. |
|
|
Availability of SQL-DMO
All required SQL-DMO components are installed as part of an
instance of Microsoft SQL Server server or client. SQL-DMO is
implemented in a single dynamic-link library (DLL). You may develop
SQL-DMO applications on either a client or a server. When using
an OLE Automation controller as a development platform, such as
Microsoft Visual Basic.NET, no additional files are required. The
following table explains the core SQLDMO files. |
| |
Directory Name
|
File Name
|
Description |
| SQL Server Folder\80\Tools\Binn |
Sqldmo.dll |
DLL implementing SQL-DMO objects. |
| SQL Server Folder\80\Tools\Binn |
Sqldmo80.hlp |
SQL-DMO help file used within the
development environment to provide context sensitive help about
SQL-DMO objects, properties and methods. |
| SQL Server Folder\80\Tools\Binn\Resources\xxxx |
Sqldmo.rll |
Localized resource file. The resource
directory varies based on the national language of the instance
of SQL Server client or server. For example, the directory 1033
is a decimal representation of the language identifier 0X0409,
indicating English, U.S. |
|
| |
| To register SQL-DMO components on
a client computer, the following registrations need to be done: |
| |
| |
From
SQL Server Folder\80\Tools\Binn\Resources\ directory, execute:
\SQL Server Folder\80\Tools\Binn\REGSVR32 SQLDMO.DLL
|
| |
From any
directory, execute: SQL Server Folder\80\Tools\Binn\REGSVR32.EXE
SQL Server Folder\80\Tools\Binn\resources\1033\SQLDMO.RLL |
|
| |
Working with SQL-DMO
Working with SQL-DMO is very simple. The object model is very structured
and orderly (the SQL Server BOL contains a complete diagram of SQL-DMO.
Its too huge to put it here, so please refer to BOL). The SQLServer
object is the core of SQL-DMO. It is through the SQLServer
object that an application connects to and alters the properties
of instances of Microsoft SQL Server. The best way to understand
SQL-DMO is via examples and in this article I've provided as
many examples as I can, but remember that there are so many possbile
scenarios that we can model with SQL-DMO that we can write whole
books about it.
Before we get into the various examples, we need to refer
to the SQLDMO DLL. Since this is a COM DLL and
we are using .NET as the development platform, we will create a
wrapper component to the DLL. The Visual Studio .NET IDE does this
automatically for us. Refer to the following diagram on what references
to add to your SQL-DMO projects. |
| |
 |
| |
Now that we have added the reference
to SQL-DMO, we are all set to start our examples. |
| |
Example - 1: Listing all
SQL Servers
One of the most common requirements for an application is to allow
users to connect to various SQL Servers using a user-name and password.
Now, in order for the user to select a SQL Server, it would be nice
if we can show all the SQL Servers available in our network so that
a user can select the appropriate server and then connect to it.
The following program shows how to get all the SQL Servers in the
network using SQL-DMO. |
| |
| Module
FirstApp
Sub Main()
Dim oApplication As SQLDMO.Application ' SQLDMO Application object
Dim oNameList As
SQLDMO.NameList ' USed to store list of servers
Dim nCounter As
Integer ' Loop counter
oApplication
= New SQLDMO.Application
Console.WriteLine("SQL
Server Version == {0}:{1}",
oApplication.VersionMajor,
oApplication.VersionMinor)
Console.WriteLine("Application
Name == {0}", oApplication.Application.Name)
Console.WriteLine()
'
Call the method that gets the list of all SQL Servers
oNameList = oApplication.ListAvailableSQLServers()
For nCounter = 1
To oNameList.Count
Console.WriteLine("{0}",
oNameList.Item(nCounter))
Next
' Important to call
this to close all connections and
' release all resources
oApplication.Quit()
'
Wait for user input
Console.ReadLine()
End Sub
End Module
|
| |
| The main method that lists all the
servers is ListAvailableSQLServers. Calling this method returns a
array of values (called a NameList). This list can then be iterated
and the values printed out (which is what the program does). We also
print some extra information like the version of SQL Server. When
you run this program, you will see an output similar to the following: |
| |
 |
| |
Example - 2: List all databases
in a SQL Server
Having seen how to get the list of SQL Servers, we will now see
how to connect to a particular server and then list all the databases
available in the server. |
| |
| Module
SecondApp
Sub Main()
Dim oSQLServer As
SQLDMO.SQLServer ' Identifies a SQL Server
Dim oDatabase As
SQLDMO.Database ' Identifies a database
oSQLServer = New
SQLDMO.SQLServer
Try
'
Connect to a particluar instance of SQL Server
oSQLServer.Connect("lp-srinivas",
"sa", "password")
'
Iterate through all the databases
For
Each oDatabase In oSQLServer.Databases
Console.WriteLine("Name
= {0}, Size = {1}", oDatabase.Name, oDatabase.Size)
Next
Catch ex As Exception
Console.WriteLine("SQLDMO
Error: " & ex.Message)
End Try
' Important to
call this to release all connections
' and release all
resources
oSQLServer.Application.Quit()
' Wait for user
input
Console.ReadLine()
End Sub
End Module
|
| |
In this program, we create a SQLServer
object and then use the Connect method to decide the
server to connect to. The Databases collection of the server
will return the list of databases present in the server. We iterate
this collection using a Database object. When you run this
program, you will see an output similar to the following: |
| |
 |
| |
| |
Example - 3: List all tables
in the Pubs database
Having seen how to list all the databases, let's see how to get
a reference to a particular database and then list all the tables
in the particular database |
| |
| Module
ThirdApp
Sub Main()
Dim oSQLServer As
SQLDMO.SQLServer ' Identifies a SQL Server
Dim oDatabase As
SQLDMO.Database ' Identifies a database
Dim oTable As SQLDMO.Table
' Identifies a table
oSQLServer = New
SQLDMO.SQLServer
Try
'
Connect to a particluar instance of SQL Server
oSQLServer.Connect("lp-srinivas",
"sa", "password")
'
Get a reference to the "pubs" database
oDatabase
= oSQLServer.Databases.Item("pubs")
'
Get the list of tables in the database
For
Each oTable In oDatabase.Tables
Console.WriteLine("{0},
# Rows = {1}", oTable.Name, oTable.Rows)
Next
Catch ex As Exception
Console.WriteLine("SQLDMO
Error: " & ex.Message)
End Try
' Important to
call this to release all connections
' and release all
resources
oSQLServer.Application.Quit()
' Wait for user
input
Console.ReadLine()
End Sub
End Module
|
| |
| The Databases collection
defines an Item property using which we can give
the name of the database to connect to. Having connected to the database,
we can use the Tables collection to get all the tables
in the database. In this program, we also print the number of rows
in each of the tables. When you run this program, you will see an
output similar to the following:
|
| |
 |
| |
Example - 4: Script out
a table
OK, till now we have seen how to query information. SQL-DMO can
also be used to generate scripts for creating various objects in
the database. In this program, we will see how to generate the CREATE
TABLE script for a table (for example "authors").
|
| |
| Module
FourthApp
Sub Main()
Dim oSQLServer As
SQLDMO.SQLServer ' Identifies a SQL Server
Dim oDatabase As
SQLDMO.Database ' Identifies a database
Dim oTable As SQLDMO.Table
' Identifies a table
Dim strResult As
String
oSQLServer = New
SQLDMO.SQLServer
Try
'
Connect to a particluar instance of SQL Server
oSQLServer.Connect("lp-srinivas",
"sa", "password")
'
Get a reference to the "pubs" database
oDatabase
= oSQLServer.Databases.Item("pubs")
'
Get a reference to the authors table
oTable
= oDatabase.Tables.Item("authors")
'
Script out the CREATE TABLE statement for the authors table
strResult
= oTable.Script( _
SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default
Or _
SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops,
_
"C:\Temp\authors.sql",
"newAuthors", _
SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default)
'
Print out the script
Console.WriteLine("{0}",
strResult)
Catch ex As Exception
Console.WriteLine("SQLDMO
Error: " & ex.Message)
End Try
' Important to
call this to release all connections
' and release all
resources
oSQLServer.Application.Quit()
' Wait for user
input
Console.ReadLine()
End Sub
End Module
|
| |
| Getting a reference to the authors
table is quite easy. We use the Item property of
the Tables collection. The important command though
is the Script method. This method is used to generate
the script for the referenced object. This command takes the following
parameters: |
| |
| |
ScriptType,
which is an optional parameter identifying the type of the script
to generate. In our example, we ask for the default creation
script along with the drop script also. |
| |
ScriptFilePath,
which is an optional parameter that identifies the location
and the file which is used to store the output of the script
command. |
| |
NewName,
which is an optional parameter that identifies the new name
for the table. The script will be created using this name ("newAuthors"
in our example). |
| |
Script2Type,
which is an optional parameter that is used to override the
default scripting options. In our example we use just the default. |
|
| |
| For more information about the constants
used in the program, please refer to the SQL Server BOL. When you
run this program, you will see an output similar to the following:
|
| |
 |
| |
| OK, till now we have seen examples
of SQL-DMO and I think you will realize the amount of flexibility
and features that an application built around SQL-DMO can provide!!
For example, you can easily write a program that can create a new
table in a database and so on. There are so many other examples that
I can think off, but I might end-up writing a book when I intended
to just write an article :-) |
| |
Conclusion
In this article we have seen how to use SQL-DMO to write
useful programs that can put tremendous amount of power at your
hands. I encourage the readers to work with SQLDMO to appreciate
its power capabilities and try as many examples as you can to see
how to automate SQL Server and its objects. Have fun!! |
| |
| For more questions, you can mail
me at srisamp@hotmail.com
or visit my web site at http://www32.brinkster.com/srisamp.
|
| |
