Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 25, 2001

More DMO

By Andy Warren

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 

 

Wrap Up

Remember, you can use DMO from any COM compliant language. Swynk reader Gregg Murray writes:

 

"You can create great administrative web pages that tell you lots of info about a multitude of jobs, servers, disk space warnings, etc... All on one freeform ASP page.  This gives you the ability to create really friendly "dashboards" that can help you perform common administrative tasks from a web page!  Ideal alternative to Enterprise manager, especially when you need to administer your SQL servers over a 28.8 modem connection on RAS!" 

Excellent point! I haven't yet used DMO with ASP, but as soon as I read that I thought of a project at work that would be perfect for using the two together - I'll let you know how it goes in a future article.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM