Av rating:
Total votes: 31
Total comments: 20
Automating Common SQL Server Tasks using DMO
27 November 2006
This article includes the source of stored procedures that will:
- List all available servers
- Enumerate all database on a Server
- Create a new database on any available server
- Copy an entire database within a server or between servers
- Copy selected tables or stored procedures between databases
- Write out a complete build script for a database
- Write out build scripts for every database on a server
- Write out a series of source files for the objects in a database in a suitable format for source-control systems
- List the database roles and the users assigned to them, for a particular database
- Checks the jobs on a server for their success or otherwise and accesses the history records
Its main intention is to show how easy it is to use DMO (and its successor, SMO) from within SQL Server, and how any COM object that does not rely on events can be used.
===+===
What follows in this article provides a taster of the sort of things you can do with DMO, showing the code to carry out the first three tasks in the above list.
All of the other procedures, with full source code, are freely available from the following link:
ACCESS ALL DMO PROCEDURES AND SOURCE CODE
===+===
When Microsoft made its decision to remove Distributed Management Objects (DMO) from SQL Server 2005, they had underestimated the extent to which it is used. DMO was the "secret" application behind Enterprise Manager and, with it, one could automate almost anything that one could do by hand with EM.
Eventually, Microsoft bowed to pressure and released "Microsoft SQL Server 2005 Backward Compatibility Components" which included the DMO. However, MSDN gives dire warnings that the DMO won't be supported much longer. SQL Server 2005 supplements SQL-DMO with two .NET based object libraries: SQL Server Management Objects (SMO) and Replication Management Objects (RMO). These components are more powerful than the DMO, and can be used with SQL 2000 and v7. Hopefully, a subsequent article will cover the SMO.
The application we originally knew as SQLOLE has survived two name-changes, but the fundamental concepts remain the same. DMO presents a simple programmatic interface via COM, so it can be used in a number of languages, including scripting Shell, PHP, VB, C# and even within SQL Server itself.
In this article, we'll be tackling the use of the DMO in SQL Server. This might seem an odd thing to want to do, but, in fact, it allows you to perform administrative functions that simply cannot be done in any other way. One must also explain why it is worth tackling a subject that has been the subject of many previous articles, and book chapters. The reason is that so many of the examples are poorly written, and make the whole process seem far more complex than it really is.
Getting started with DMO: listing all available servers
Busy DBAs can't afford to perform routine processes manually. There just isn't time. Everything has to be scripted, automated and scheduled, with a system of checks and alerts. Whenever I find myself doing something with SSMS, or EM, more than a couple of times, I script the task and then automate it. Quite often, the scripting is done using SQL Server.
The DMO COM interface consists of:
- sp_OAGetProperty which gets the value of a property.
- sp_OASetProperty which sets the value of a property.
- sp_OAMethod which calls a method.
- sp_OAGetErrorInfo which obtains the most recent error information.
There is no way to handle events from COM objects. The best way of getting started with DMO is via a simple example. This procedure creates a result with the names of all the available servers, just as appears in the drop-down list in Enterprise Manager when registering a server:
CREATE PROCEDURE spAllAvailableServers
AS
DECLARE @hr INT, --the HRESULT returned from the OLE operation
@ii INT, --a simple counter
@iimax INT, --the max of the iteration
@objApplication INT, --the application object
@ErrorObject INT, --the error object
@ErrorMessage VARCHAR(255),--the potential error message
@command VARCHAR(255), --the command
@Server VARCHAR(255) --The String with the current server
SET nocount ON
EXEC @hr = sp_OACreate 'SQLDMO.Application', @objApplication OUT
SELECT @errorMessage=
'Getting the number of available servers in the collection',
@ErrorObject=@objApplication
IF @HR=0 EXEC @hr = sp_OAGetProperty @objApplication,
'ListAvailableSQLServers.Count',
@iimax OUT
SELECT @errorMessage='Getting each item',@ii=1
CREATE TABLE #Servers (MyID INT IDENTITY(1,1), Server VARCHAR(255))
WHILE @hr=0 AND @ii<=@iiMax --FOR EACH in
BEGIN
SELECT @command='ListAvailableSQLServers.item
('+CAST (@ii AS VARCHAR)+')'
EXEC @hr = sp_OAGetProperty @objApplication, @command,
@Server OUT
INSERT INTO #servers(server) SELECT @Server
SELECT @ii=@ii+1
END
--shared error-handling code
IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @errorObject,
@source output,@Description output,
@Helpfile output,@HelpID output
SELECT @ErrorMessage='Error whilst '+@Errormessage+', '+@Description
RAISERROR (@ErrorMessage,16,1)
END
EXEC sp_OADestroy @objApplication
SELECT * FROM #servers
RETURN @hr
GO
Check it out by simply executing exec spAllAvailableServers (note that you may need to enable OLE Automation objects, via Surface Area Configuration for Features)
Notice that there are no GOTO statements that one sees so often in such code. Also there are no cursors. These aren't necessary and they bulk up the code. This is a vital factor when you are creating more complex DMO procedures.
The first step when using an OLE Automation object in Transact-SQL is to call the sp_OACreate system stored procedure to create an instance of the object in the address space of the instance of the Database Engine. In this case, it is the SQLDMO application object. Then we just get from it the list of available SQL Servers. If an automation error occurs, the code drops through to the error handler and thence out, passing the HRESULT back to the calling procedure, but still providing an empty result.
Enumerating all databases on a Server
So what about adapting an example application from VB? Here is one that enumerates all the databases on a server, and gives their size in Mb (from Rick Dobson 12.06.2000):
Sub call_enumerate_databases()
'Pass along server name, login, and password
'to routine to enumerate databases on a server
enumerate_databases "cabarmada", "sa", ""
End Sub
Sub enumerate_databases(srvname As String, _
login As String, password As String)
Dim srv1 As SQLDMO.SQLServer
Dim dbs As SQLDMO.Database
'Connect to a SQL Server
Set srv1 = New SQLDMO.SQLServer
srv1.Connect srvname, login, password
'Enumerate the databases on connected server
For Each dbs In SQLDMO.SQLServers(srvname).Databases
Debug.Print dbs.Name & " uses " & dbs.Size & _
"MB of storage."
Next
'Cleanup routine
srv1.Disconnect
Set srv1 = Nothing
End Sub
We can use this code as the basis for a stored procedure, which will do exactly the same thing:
CREATE PROCEDURE spEnumerateDatabases
@strServer VARCHAR(100),
@login VARCHAR(100)=NULL,
@password VARCHAR(100)=NULL
/*
--SQL Server Authentication
execute spEnumerateDatabases 'MyServer','MyLogin','MyPassword'
--Windows Authentication
execute spEnumerateDatabases 'MyServer'
*/
AS
DECLARE @hr INT, --the HRESULT returned from the OLE operation
@ii INT, --a simple counter
@iimax INT, --the max of the iteration
@objServer INT, --the Server object
@objDatabase INT, --the Database object
@ErrorObject INT, --the error object
@ErrorMessage VARCHAR(255), --the potential error message
@command VARCHAR(255), --the command
@DatabaseName VARCHAR(255), --the current Database name
@DatabaseSize INT --the current Database size
SET nocount ON
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
IF @password IS NULL OR @login IS NULL
BEGIN
--use a trusted connection
IF @hr=0 SELECT @ErrorMessage=
'Setting login to windows authentication on '
+@strServer,
@ErrorObject=@objServer
IF @hr=0 EXEC @hr = sp_OASetProperty @objServer, 'LoginSecure', 1
IF @hr=0 SELECT @ErrorMessage=
'logging in to the requested server using
windows authentication on '+@strServer
IF @login IS NULL AND @hr=0 EXEC @hr = sp_OAMethod @objServer,
'Connect', NULL, @strServer
IF @login IS NOT NULL AND @hr=0 EXEC @hr = sp_OAMethod @objServer,
'Connect', NULL, @strServer ,@Login
END
ELSE
BEGIN
IF @hr=0 SELECT @ErrorMessage = 'Connecting to '''+@strServer+
''' with user ID '''+@login+'''', @ErrorObject=@objServer
IF @hr=0 EXEC @hr = sp_OAMethod @objServer,
'Connect', NULL, @strServer,
@login , @password
END
SELECT @errorMessage='finding the number of databases in '+@strServer,
@ErrorObject=@objServer
IF @HR=0 EXEC @hr = sp_OAGetProperty @objServer, 'databases.Count',
@iimax OUT
SELECT @errorMessage='Getting each item',@ii=1
CREATE TABLE #Databases (MyID INT IDENTITY(1,1),
TheName VARCHAR(255),theSize INT)
WHILE @hr=0 AND @ii<=@iiMax
BEGIN
SELECT @command='databases.item('+CAST (@ii AS VARCHAR)+').name'
EXEC @hr = sp_OAGetProperty @objServer, @command,
@DatabaseName OUT
SELECT @command='databases.item('+CAST (@ii AS VARCHAR)+').size'
IF @HR=0 EXEC @hr = sp_OAGetProperty @objServer, @command,
@DatabaseSize OUT
INSERT INTO #Databases(thename,thesize)
SELECT @DatabaseName,@DatabaseSize
SELECT @ii=@ii+1
END
--and handling any errors
IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @errorObject,
@source output,@Description output,
@Helpfile output,@HelpID output
SELECT @ErrorMessage='Error whilst '+@Errormessage+', '+@Description
RAISERROR (@ErrorMessage,16,1)
END
EXEC sp_OADestroy @objServer
SELECT * FROM #databases
RETURN @hr
Of course, in a working version, you'd want the available space in MB (SpaceAvailableInMB), the Status (e.g. Standby, Normal, Recovering etc) and a host of other information useful to the DBA. It is a simple matter of exploring the DMO Object model on MSDN and seeing what's available.
Create a database on any server
For this, we can take the MSDN example for creating a database as our basis:
Dim oDatabase As New SQLDMO.Database
Dim oDBFileData As New SQLDMO.DBFile
Dim oLogFile As New SQLDMO.LogFile
oDatabase.Name = "Northwind"
' Define the PRIMARY data file.
oDBFileData.Name = "NorthData1"
oDBFileData.PhysicalName = _
"c:\program files\microsoft sql server\mssql\data\northwnd.mdf"
oDBFileData.PrimaryFile = True
' Specify file growth in chunks of fixed size for all data files.
oDBFileData.FileGrowthType = SQLDMOGrowth_MB
oDBFileData.FileGrowth = 1
oDatabase.FileGroups("PRIMARY").DBFiles.Add oDBFileData
' Define the database transaction log.
oLogFile.Name = "NorthLog1"
oLogFile.PhysicalName = _
"c:\program files\microsoft sql server\mssql\data\northwnd.ldf"
oDatabase.TransactionLog.LogFiles.Add oLogFile
' Create the database as defined. Note: Create and connect of SQLServer
' object used is not illustrated in this example.
oSQLServer.Databases.Add oDatabase
A simple conversion of this routine allows us to create a database on any of the servers within our ‘stamping ground':
CREATE PROCEDURE spCreateDatabase
@strServer VARCHAR(100), --the name of the database server
@login VARCHAR(100), --the login
@password VARCHAR(100), --the password
@DatabaseName VARCHAR(100),--the name of the database to create
@DataFileName VARCHAR(100),--the name of the data file
@DataFilePath VARCHAR(100),--the full path and name of the file
@LogFileName VARCHAR(100), --the name of the log file
@LogFilePath VARCHAR(100) --the full path and name of the log file
/*
--with SQL Server Authentication
spCreateDatabase 'Server',MyUserID,MyPassword,
'MyDatabaseName',
'MyLogicalDataFileName',
'MyPhysicalDataPathandfilename',
'MyLogicalLogFileName',
'MyPhysicalLogPathandfilename'
--with Windows Authentication
spCreateDatabase 'Server',default,default,
'MyDatabaseName',
'MyLogicalDataFileName',
'MyPhysicalDataPathandfilename',
'MyLogicalLogFileName',
'MyPhysicalLogPathandfilename'
*/
AS
DECLARE @hr INT, --the HRESULT returned from the OLE operation
@ii INT, --a simple counter
@iimax INT, --the max of the iteration
@objServer INT, --the Server object
@objDatabase INT, --the Database object
@objDBfileData INT, --the Database file
@objLogFile INT, --the log file
@ErrorObject INT, --the error object
@strErrorMessage VARCHAR(255)--the potential error message
--and our 'constants'
DECLARE @SQLDMOGrowth_MB INT SET @SQLDMOGrowth_MB = 0
DECLARE @SQLDMOGrowth_Percent INT SET @SQLDMOGrowth_Percent = 1
DECLARE @SQLDMOGrowth_Invalid INT SET @SQLDMOGrowth_Invalid = 99
SET nocount ON
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
IF @password IS NULL OR @login IS NULL
BEGIN
--use a trusted connection
IF @hr=0 SELECT @strErrorMessage