In my previous article (
Intro to DMO), I described how to use DMO to connect to
SQL Server and do some basic tasks, such as performing a backup. This article
will introduce some additional methods that are very useful when automating
administrative tasks. All code has been tested on SQL 2000, but should work fine
with SQL 7.
ExecuteImmediate Method
The ExecuteImmediate gives
you the ability to execute T-SQL or stored procedures from within your DMO
script. If you use ExecuteImmediate as a method of a database object, you get
the same effect as if you had executed a “Use Database” in QueryAnalyzer. You
can also use ExecuteImmediate as a method of the server object, in which case the
database is always the master. Assuming you’ve already
established a connection to your server, this sample code will update
statistics on all objects in all databases.
|
Dim oServer
'As SQLDMO.SQLServer
Dim oDatabase
'As SQLDMO.Database
Set oServer =
CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect "(local)"
For Each oDatabase In oServer.Databases
'2=SQLDMOExec_ContinueOnError
oDatabase.ExecuteImmediate "sp_updatestats", 2
Next
'clean up
oServer.DisConnect
Set oServer = Nothing
|
ExecuteWithResults Method and the QueryResults
Object
ExecuteWithResults works
just like the ExecuteImmediate method, except you have to assign the results of
the method to a QueryResults object. The QueryResults is my least favorite
object. Instead of returning an ADO recordset, or at least a true object that
would support for/each interation, it is essentially an array. To make matters
worse, you have to use different methods to retrieve column values depending on
the column datatype. Still, it is good enough for most admin tasks and allows
you to work solely within DMO without having to have any knowledge of other
object models.
|
Dim oServer
'As SQLDMO.SQLServer
Dim oDatabase
'As SQLDMO.Database
Dim oResults
'As SQLDMO.QueryResults
Dim lCount
'As Long
Dim sMessage
'As String
Dim
SQL
'As String
Dim
J
'As Long
Set oServer =
CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect "(local)"
SQL = "Select Name from SysUsers where
IsSQLRole=0 order by Name"
For Each oDatabase In oServer.Databases
Set oResults =
oDatabase.ExecuteWithResults(SQL)
sMessage
= "Users for database: " & oDatabase.Name & Chr(13) &
Chr(10)
For J = 1
To oResults.Rows
sMessage =
sMessage & oResults.GetColumnString(J, 1) & Chr(13) & Chr(10)
Next
sMessage
= sMessage & "There are " & oResults.Rows & "
users"
Set
oResults = Nothing
MsgBox
sMessage
Next
'clean up
oServer.DisConnect
Set oServer = Nothing
|
Script Method
Think of the scripting
options available in Enterprise Manager. You can reproduce them all in DMO,
plus some! In this example I'm creating one script per database containing all
of it's views. I'm using the appendtofile flag so that each time I script an
object, it doesn't overwrite the previous script. The primaryobject flag is the
one that tells DMO to generate the DDL for the object.
This example also makes use
of the CommandShellImmediate method – which directly corresponds to xp_cmdshell.
Take a look also at the nested loops, the outer one for the databases, the
inner for the views – objects make this kind of looping incredibly easy.
|
Dim oServer
'As SQLDMO.SQLServer
Dim oDatabase
'As SQLDMO.Database
Dim oView
'As SQLDMO.View
Set oServer =
CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect "(local)"
'this deletes previous versions of scripts - use
with care!
oServer.CommandShellImmediate "Delete
C:\DMO_Views*.sql"
'loop through each view in each database, creating
one script per database
'to create all of the views
For Each oDatabase In oServer.Databases
For Each
oView In oDatabase.Views
'SQLDMOScript_AppendToFile=8192
'SQLDMOScript_ObjectPermissions=2
'SQLDMOScript_ToFileOnly=64
'SQLDMOScript_PrimaryObject=4
oView.Script 8192 + 2 + 64 + 4, "C:\DMO_Views_" &
oDatabase.Name & ".sql"
Next
Next
'clean up
oServer.DisConnect
Set oServer = Nothing
Msgbox
"Done."
|
ListAvailableSQLServers
Method
This method returns a
NameList object – a collection object in which the members are not strongly
typed. In order to iterate the collection using the for/each syntax, you can
use a variable of type variant for the member object. In this example I'm
showing how you can retrieve the number of databases for each SQL Server that
is visible on the network.
|
Dim oApp
'As SQLDMO.Application
Dim oServer
'As SQLDMO.SQLServer
Dim oDatabase
'As SQLDMO.Database
Dim oNames
'As SQLDMO.NameList
Dim oName
'As Variant
Set oApp =
CreateObject("SQLDMO.Application")
Set oNames = oApp.ListAvailableSQLServers()
For Each oName In oNames
Set oServer =
CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect oName
MsgBox
"There are " & oServer.Databases.Count & " databases for
server " & oName
oServer.DisConnect
Set
oServer = Nothing
Next
'clean up
oApp.Quit
Set oApp = Nothing
|
