SQL-DMO (Distributed
Management Objects) provides a programmatic (COM) interface to SQL Server.
Its a great tool for automating routine tasks far more flexible than
T-SQL in most cases. In this article Ill demonstrate several uses of DMO that
will give you a starting point for building your own solutions. Ill be using
VB Script for these examples, but you can use any language that supports COM.
Code has been tested on SQL 2000, but should work fine in SQL 7
I like to use VB to code
my DMO scripts, then convert to VB Script. If you use this method, a few things
to remember:
–
Set a reference to Microsoft SQL-DMO so
you get Intellisense
–
Use CreateObject instead of New when
creating your objects VB Script doesnt support New
–
Use the variant model functions (CHR,
UCASE, etc) instead of the ones optimized for strings (CHR$,UCASE$) VB
Script doesnt support the string versions
–
Convert constants to their actual values
–
Remove all data typing. A handy trick is
to just comment out the type declaration, so you can more easily read the code
and you can always uncomment them if you dump the code back into VB to make
changes
If youre just using
Notepad to write your scripts, add a few lines at a time and test. Use Msgbox to
help you debug and trace your program flow. Scripts should be saved with the
standard .VBS extension you can execute them by double clicking
Example #1 How many
databases do you have?
This shows how to connect
to SQL running on your local machine, display the number of databases, then
disconnect
Dim oServer
‘get a reference to the DMO object (sqldmo.dll)
Set oServer =
CreateObject("SQLDmo.SqlServer")
‘use a trusted connection
oServer.LoginSecure = True
oServer.Connect
‘this displays the count should be the
same count youd get doing
‘select count(*) from master..sysdatabases
MsgBox oServer.Databases.Count
‘always clean up when youre done
oServer.DisConnect
Set oServer = Nothing
Example #2 How much
space is available in each database?
This script cycles through
each database and returns the free space in megabytes. Note that this time Im
specifying the server to connect to rather than relying on the default behavior
of connecting to the local machine
Dim oServer
Dim oDatabase
Dim sResults
Set oServer =
CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
‘this time Im specifying which server to
connect to
oServer.Connect "Andy"
‘this will return the name of each database
along with the total free
‘space in megabytes. The Chr(9) is a tab,
the chr(13) and the chr(10) map
‘to carriage return and line feed
respectively
For Each oDatabase In oServer.Databases
sResults = sResults & oDatabase.Name & Chr(9) &
oDatabase.SpaceAvailableInMB & Chr(13) & Chr(10)
Next
‘show the answer – could easily send using
xp_sendmail if we wanted
MsgBox sResults
oServer.DisConnect
Set oServer = Nothing
Example #3 Update Statistics if Auto Update is Disabled
You arent restricted to
just reading information, you can make changes or take action as well. In this
example Im going to update the statistics for any database that has the auto
update option disabled. At the end Im adding some additional text to the
sResult variable to make the display a little more informative
Dim oServer
Dim oDatabase
Dim sResults
Set oServer =
CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
‘this time Im specifying which server to
connect to
oServer.Connect "Andy"
‘if auto update stats is turned off, update
the statistics and return a list of those
‘databases
For Each oDatabase In oServer.Databases
If oDatabase.DBOption.AutoUpdateStat = False Then
sResults = sResults & oDatabase.Name & Chr(13) & Chr(10)
oDatabase.UpdateIndexStatistics
End If
Next
‘here Im making the output a little fancier
If sResults <> "" Then
sResults = "Statistics were updated for the following databases:
" & Chr(13) & Chr(10) & sResults
Else
sResults = "No databases had auto update statistics turned
off."
End If
MsgBox sResults
oServer.DisConnect
Set oServer = Nothing
Example #4 Backup All Databases
Dim oServer
Dim oDatabase
Dim oBackup
Dim sBAKFilePath
'change this to where ever you want
to place your backup files, no trailing
'backslash, we add it below
sBAKFilePath = "C:Backup"
'we need a backup object in addition
to the sqlserver one
Set oServer =
CreateObject("SQLDmo.SqlServer")
Set oBackup =
CreateObject("SQLDmo.Backup")
oServer.LoginSecure = True
oServer.Connect "(local)"
'this will do a full backup of every
database except TempDB to a file (not a
'device
For Each oDatabase In
oServer.Databases
If UCase(oDatabase.Name) <> "TEMPDB" Then
oBackup.Database = oDatabase.Name
'remove any previous backup - same as using T-SQL with init
oBackup.Initialize = True
'dynamically create the name of the backup file
oBackup.Files = sBAKFilePath & "" &
oDatabase.Name & ".bak"
'set the action property as needed
'0 = Full backup
'1 = Differential
'2 = Specified files only
'3 = Log backup
oBackup.Action = 0
oBackup.SQLBackup oServer
End If
Next
'clean up
Set oBackup = Nothing
oServer.DisConnect
Set oServer = Nothing
Deploying Your Scripts
Once youve gotten your
code to work successfully, there are several ways to put it to work:
7
Leave it in a VBS file (or
compile into an executable) to be run manually on as needed basis
7
Add it as a step to SQL job
select ActiveX Script as the step type. Keep in mind that the size of the step
is limited to about 3200 chars (the size of the command column in sysjobsteps).
7
Schedule the VBS/EXE to run as a
OS scheduled task or as a SQL job of type CmdExec
If you play to run your
code on the server, comment out or remove any lines that use Msgbox, otherwise
your script will be stalled waiting on the user to click OK. Even worse, if you
run the script as a SQL Agent ActiveX Script job, the message box will be
suppressed so the server operator cannot see it, but the job will still be
waiting on user input
One other technique you
may find useful is to include Print statements in your script. These will
fail when you test your script in VB or VB Script, but when run under SQL Agent
the output of the print statement will be entered into the job history. Example
#1 might be modified as follows:
Replace
MsgBox
oServer.Databases.Count
With
Print
Count= & oServer.databases.count
References
Books
Online has an object model diagram that is very useful (search for SQL-DMO,
object tree in SQL 2000). You can also find some useful articles at www.devx.com
and www.sqlmag.com.
