SQLServerCentral Article

More Ideas About Using SQL-DMO

,

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 

 

 

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating