{"id":187,"date":"2006-11-27T00:00:00","date_gmt":"2006-11-27T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/automating-common-sql-server-tasks-using-dmo\/"},"modified":"2021-08-24T13:40:47","modified_gmt":"2021-08-24T13:40:47","slug":"automating-common-sql-server-tasks-using-dmo","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/automating-common-sql-server-tasks-using-dmo\/","title":{"rendered":"Automating Common SQL Server Tasks using DMO"},"content":{"rendered":"<p><em>This article includes the source of stored procedures that will:<\/em><\/p>\n<ol>\n<li><em>List all available servers<\/em><\/li>\n<li><em>Enumerate all database on a Server<\/em><\/li>\n<li><em>Create a new database on any available server<\/em><\/li>\n<li><em>Copy an entire database within a server or between servers<\/em><\/li>\n<li><em>Copy selected tables or stored procedures between databases<\/em><\/li>\n<li><em>Write out a complete build script for a database<\/em><\/li>\n<li><em>Write out build scripts for every database on a server<\/em><\/li>\n<li><em>Write out a series of source files for the objects in a database in a suitable format for source-control systems<\/em><\/li>\n<li><em>List the database roles and the users assigned to them, for a particular database<\/em><\/li>\n<li><em>Checks the jobs on a server for their success or otherwise and accesses the history records<\/em><\/li>\n<\/ol>\n<p><em>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.<\/em><\/p>\n<p><em>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/em><strong>===+===<\/strong><\/p>\n<p>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.<\/p>\n<p>All of the other procedures, <strong>with full source code<\/strong>, are freely available from the following link:<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/database-administration\/more-database-administration-and-development-automation-using-dmo\/\">ACCESS ALL DMO PROCEDURES AND SOURCE CODE<\/a><\/p>\n<p><em>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/em><strong>===+===<\/strong><\/p>\n<p>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 &#8220;secret&#8221; application behind Enterprise Manager and, with it, one could automate almost anything that one could do by hand with EM.<\/p>\n<p>Eventually, Microsoft bowed to pressure and released &#8220;Microsoft SQL Server 2005 Backward Compatibility Components&#8221; which included the DMO. However, MSDN gives dire warnings that the DMO won&#8217;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.<\/p>\n<p>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.<\/p>\n<p>In this article, we&#8217;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.<\/p>\n<h2>Getting started with DMO: listing all available servers<\/h2>\n<p>Busy DBAs can&#8217;t afford to perform routine processes manually. There just isn&#8217;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.<\/p>\n<p>The DMO COM interface consists of:<\/p>\n<ul>\n<li><b>sp_OAGetProperty<\/b> which gets the value of a property.<\/li>\n<li><b>sp_OASetProperty<\/b> which sets the value of a property.<\/li>\n<li><b>sp_OAMethod<\/b> which calls a method.<\/li>\n<li><b>sp_OAGetErrorInfo<\/b> which obtains the most recent error information.<\/li>\n<\/ul>\n<p>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:<\/p>\n<pre>CREATE\u00a0PROCEDURE\u00a0spAllAvailableServers \r\nAS \r\nDECLARE\u00a0@hr\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0HRESULT\u00a0returned\u00a0from\u00a0the\u00a0OLE\u00a0operation \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ii\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--a\u00a0simple\u00a0counter \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@iimax\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0max\u00a0of\u00a0the\u00a0iteration \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@objApplication\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0application\u00a0object \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ErrorObject\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0error\u00a0object \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ErrorMessage\u00a0VARCHAR(255),--the\u00a0potential\u00a0error\u00a0message \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@command\u00a0VARCHAR(255),\u00a0\u00a0\u00a0--the\u00a0command \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@Server\u00a0VARCHAR(255)\u00a0\u00a0\u00a0\u00a0\u00a0--The\u00a0String\u00a0with\u00a0the\u00a0current\u00a0server \r\nSET\u00a0nocount\u00a0ON \r\nEXEC\u00a0@hr\u00a0=\u00a0sp_OACreate\u00a0'SQLDMO.Application',\u00a0@objApplication\u00a0OUT \r\nSELECT\u00a0@errorMessage=\r\n'Getting\u00a0the\u00a0number\u00a0of\u00a0available\u00a0servers\u00a0in\u00a0the\u00a0collection', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ErrorObject=@objApplication \r\nIF\u00a0@HR=0\u00a0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAGetProperty\u00a0@objApplication,\r\n'ListAvailableSQLServers.Count',\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0                @iimax\u00a0OUT \r\nSELECT\u00a0@errorMessage='Getting\u00a0each\u00a0item',@ii=1 \r\nCREATE\u00a0TABLE\u00a0#Servers\u00a0(MyID\u00a0INT\u00a0IDENTITY(1,1),\u00a0Server\u00a0VARCHAR(255)) \r\nWHILE\u00a0@hr=0\u00a0AND\u00a0@ii&lt;=@iiMax\u00a0--FOR\u00a0EACH\u00a0in \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0@command='ListAvailableSQLServers.item\r\n                         ('+CAST\u00a0(@ii\u00a0AS\u00a0VARCHAR)+')' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAGetProperty\u00a0@objApplication,\u00a0@command,\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@Server\u00a0OUT \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INSERT\u00a0INTO\u00a0#servers(server)\u00a0SELECT\u00a0@Server \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0@ii=@ii+1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END \r\n--shared\u00a0error-handling\u00a0code \r\nIF\u00a0@hr&lt;&gt;0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DECLARE\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@Source\u00a0VARCHAR(255), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@Description\u00a0VARCHAR(255), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@Helpfile\u00a0VARCHAR(255), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@HelpID\u00a0INT \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0EXECUTE\u00a0sp_OAGetErrorInfo\u00a0\u00a0@errorObject,\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@source\u00a0output,@Description\u00a0output,\r\n@Helpfile\u00a0output,@HelpID\u00a0output \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0@ErrorMessage='Error\u00a0whilst\u00a0'+@Errormessage+',\u00a0'+@Description \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0RAISERROR\u00a0(@ErrorMessage,16,1) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END \r\nEXEC\u00a0sp_OADestroy\u00a0@objApplication \r\nSELECT\u00a0*\u00a0FROM\u00a0#servers \r\nRETURN\u00a0@hr \r\nGO \u00a0<\/pre>\n<p>Check it out by simply executing <b>exec<\/b><b> <\/b><code><b>spAllAvailableServers<\/b><\/code> (note that you may need to enable OLE Automation objects, via Surface Area Configuration for Features) Notice that there are no <b>GOTO<\/b> statements that one sees so often in such code. Also there are no cursors. These aren&#8217;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 <b>sp_OACreate<\/b> 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 <b>HRESULT<\/b> back to the calling procedure, but still providing an empty result.<\/p>\n<h2>Enumerating all databases on a Server<\/h2>\n<p>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):\u00a0<\/p>\n<pre>Sub call_enumerate_databases()\r\n\r\n'Pass along server name, login, and password\r\n'to routine to enumerate databases on a server\r\nenumerate_databases \"cabarmada\", \"sa\", \"\"\r\n\r\nEnd Sub\r\n\r\nSub enumerate_databases(srvname As String, _\r\n\u00a0\u00a0\u00a0 login As String, password As String)\r\nDim srv1 As SQLDMO.SQLServer\r\nDim dbs As SQLDMO.Database\r\n\r\n'Connect to a SQL Server\r\nSet srv1 = New SQLDMO.SQLServer\r\nsrv1.Connect srvname, login, password\r\n\r\n'Enumerate the databases on connected server\r\nFor Each dbs In SQLDMO.SQLServers(srvname).Databases\r\n\u00a0\u00a0\u00a0 Debug.Print dbs.Name &amp; \" uses \" &amp; dbs.Size &amp; _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"MB of storage.\"\r\nNext\r\n\r\n'Cleanup routine\r\nsrv1.Disconnect\r\nSet srv1 = Nothing\r\n\r\nEnd Sub<\/pre>\n<p>We can use this code as the basis for a stored procedure, which will do exactly the same thing:<\/p>\n<pre>CREATE\u00a0PROCEDURE\u00a0spEnumerateDatabases \r\n@strServer\u00a0VARCHAR(100), \r\n@login\u00a0\u00a0VARCHAR(100)=NULL, \r\n@password\u00a0VARCHAR(100)=NULL \r\n\r\n\/* \r\n--SQL\u00a0Server\u00a0Authentication \r\nexecute\u00a0spEnumerateDatabases\u00a0'MyServer','MyLogin','MyPassword' \r\n--Windows\u00a0Authentication \r\nexecute\u00a0spEnumerateDatabases\u00a0'MyServer' \r\n\r\n*\/ \r\nAS \r\nDECLARE\u00a0@hr\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --the\u00a0HRESULT\u00a0returned\u00a0from\u00a0the\u00a0OLE\u00a0operation \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ii\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --a\u00a0simple\u00a0counter \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@iimax\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --the\u00a0max\u00a0of\u00a0the\u00a0iteration \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@objServer\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --the\u00a0Server\u00a0object \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@objDatabase\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --the\u00a0Database\u00a0object \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ErrorObject\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --the\u00a0error\u00a0object \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ErrorMessage\u00a0VARCHAR(255), --the\u00a0potential\u00a0error\u00a0message \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@command\u00a0VARCHAR(255),\u00a0\u00a0\u00a0 --the\u00a0command \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@DatabaseName\u00a0VARCHAR(255),\u00a0--the\u00a0current\u00a0Database\u00a0name \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@DatabaseSize\u00a0INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0--the\u00a0current\u00a0Database\u00a0size \r\n\r\nSET\u00a0nocount\u00a0ON \r\n\r\nEXEC\u00a0@hr\u00a0=\u00a0sp_OACreate\u00a0'SQLDMO.SQLServer',\u00a0@objServer\u00a0OUT \r\nIF\u00a0@password\u00a0IS\u00a0NULL\u00a0OR\u00a0@login\u00a0IS\u00a0NULL \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--use\u00a0a\u00a0trusted\u00a0connection\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@hr=0\u00a0SELECT\u00a0@ErrorMessage=\r\n'Setting\u00a0login\u00a0to\u00a0windows\u00a0authentication\u00a0on\u00a0'\r\n+@strServer, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ErrorObject=@objServer \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OASetProperty\u00a0@objServer,\u00a0'LoginSecure',\u00a01 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@hr=0\u00a0SELECT\u00a0@ErrorMessage=\r\n'logging\u00a0in\u00a0to\u00a0the\u00a0requested\u00a0server\u00a0using\r\n windows\u00a0authentication\u00a0on\u00a0'+@strServer \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@login\u00a0IS\u00a0NULL\u00a0AND\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAMethod\u00a0@objServer,\r\n'Connect',\u00a0NULL,\u00a0@strServer\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@login\u00a0IS\u00a0NOT\u00a0NULL\u00a0AND\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAMethod\u00a0@objServer,\r\n'Connect',\u00a0NULL,\u00a0@strServer\u00a0,@Login \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END \r\nELSE \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@hr=0\u00a0SELECT\u00a0@ErrorMessage\u00a0=\u00a0'Connecting\u00a0to\u00a0'''+@strServer+ \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'''\u00a0with\u00a0user\u00a0ID\u00a0'''+@login+'''',\u00a0@ErrorObject=@objServer \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAMethod\u00a0@objServer,\r\n'Connect',\u00a0NULL,\u00a0@strServer, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @login\u00a0,\u00a0@password \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END \r\nSELECT\u00a0@errorMessage='finding\u00a0the\u00a0number\u00a0of\u00a0databases\u00a0in\u00a0'+@strServer, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ErrorObject=@objServer \r\nIF\u00a0@HR=0\u00a0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAGetProperty\u00a0@objServer,\u00a0'databases.Count',\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@iimax\u00a0OUT \r\nSELECT\u00a0@errorMessage='Getting\u00a0each\u00a0item',@ii=1 \r\nCREATE\u00a0TABLE\u00a0#Databases\u00a0(MyID\u00a0INT\u00a0IDENTITY(1,1),\r\nTheName\u00a0VARCHAR(255),theSize\u00a0INT) \r\nWHILE\u00a0@hr=0\u00a0AND\u00a0@ii&lt;=@iiMax \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0@command='databases.item('+CAST\u00a0(@ii\u00a0AS\u00a0VARCHAR)+').name' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAGetProperty\u00a0@objServer,\u00a0@command,\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@DatabaseName\u00a0OUT \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0@command='databases.item('+CAST\u00a0(@ii\u00a0AS\u00a0VARCHAR)+').size' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@HR=0\u00a0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAGetProperty\u00a0@objServer,\u00a0@command,\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@DatabaseSize\u00a0OUT \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INSERT\u00a0INTO\u00a0#Databases(thename,thesize)\r\nSELECT\u00a0@DatabaseName,@DatabaseSize \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0@ii=@ii+1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END\r\n--and\u00a0handling\u00a0any\u00a0errors\r\nIF\u00a0@hr&lt;&gt;0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DECLARE\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@Source\u00a0VARCHAR(255), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@Description\u00a0VARCHAR(255), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@Helpfile\u00a0VARCHAR(255), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@HelpID\u00a0INT \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0EXECUTE\u00a0sp_OAGetErrorInfo\u00a0\u00a0@errorObject,\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@source\u00a0output,@Description\u00a0output,\r\n@Helpfile\u00a0output,@HelpID\u00a0output \r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0@ErrorMessage='Error\u00a0whilst\u00a0'+@Errormessage+',\u00a0'+@Description \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0RAISERROR\u00a0(@ErrorMessage,16,1) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END \r\nEXEC\u00a0sp_OADestroy\u00a0@objServer \r\nSELECT\u00a0*\u00a0FROM\u00a0#databases \r\nRETURN\u00a0@hr<\/pre>\n<p>Of course, in a working version, you&#8217;d want the available space in MB (<b>SpaceAvailableInMB<\/b>), 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&#8217;s available.<\/p>\n<h2>Create a database on any server<\/h2>\n<p>For this, we can take the MSDN example for creating a database as our basis:<\/p>\n<pre>Dim oDatabase As New SQLDMO.Database\r\nDim oDBFileData As New SQLDMO.DBFile\r\nDim oLogFile As New SQLDMO.LogFile\r\n\r\noDatabase.Name = \"Northwind\"\r\n\r\n' Define the PRIMARY data file.\r\noDBFileData.Name = \"NorthData1\"\r\noDBFileData.PhysicalName = _ \r\n\u00a0\u00a0\u00a0 \"c:\\program files\\microsoft sql server\\mssql\\data\\northwnd.mdf\"\r\noDBFileData.PrimaryFile = True\r\n\r\n' Specify file growth in chunks of fixed size for all data files.\r\noDBFileData.FileGrowthType = SQLDMOGrowth_MB\r\noDBFileData.FileGrowth = 1\r\n\r\noDatabase.FileGroups(\"PRIMARY\").DBFiles.Add oDBFileData\r\n\r\n' Define the database transaction log.\r\noLogFile.Name = \"NorthLog1\"\r\noLogFile.PhysicalName = _ \r\n\u00a0\u00a0\u00a0 \"c:\\program files\\microsoft sql server\\mssql\\data\\northwnd.ldf\"\r\noDatabase.TransactionLog.LogFiles.Add oLogFile\r\n\r\n' Create the database as defined. Note: Create and connect of SQLServer\r\n' object used is not illustrated in this example.\r\noSQLServer.Databases.Add oDatabase<\/pre>\n<p>A simple conversion of this routine allows us to create a database on any of the servers within our &#8216;stamping ground&#8217;:<\/p>\n<pre>CREATE\u00a0PROCEDURE\u00a0spCreateDatabase \r\n@strServer\u00a0VARCHAR(100),\u00a0\u00a0\u00a0--the\u00a0name\u00a0of\u00a0the\u00a0database\u00a0server \r\n@login\u00a0\u00a0VARCHAR(100),\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0login \r\n@password\u00a0VARCHAR(100),\u00a0\u00a0\u00a0\u00a0--the\u00a0password \r\n@DatabaseName\u00a0VARCHAR(100),--the\u00a0name\u00a0of\u00a0the\u00a0database\u00a0to\u00a0create \r\n@DataFileName\u00a0VARCHAR(100),--the\u00a0name\u00a0of\u00a0the\u00a0data\u00a0file \r\n@DataFilePath\u00a0VARCHAR(100),--the\u00a0full\u00a0path\u00a0and\u00a0name\u00a0of\u00a0the\u00a0file \r\n@LogFileName\u00a0VARCHAR(100),\u00a0--the\u00a0name\u00a0of\u00a0the\u00a0log\u00a0file \r\n@LogFilePath\u00a0VARCHAR(100)\u00a0\u00a0--the\u00a0full\u00a0path\u00a0and\u00a0name\u00a0of\u00a0the\u00a0log\u00a0file \r\n\r\n\/* \r\n--with\u00a0SQL\u00a0Server\u00a0Authentication \r\nspCreateDatabase\u00a0'Server',MyUserID,MyPassword, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'MyDatabaseName', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'MyLogicalDataFileName', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'MyPhysicalDataPathandfilename', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'MyLogicalLogFileName', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'MyPhysicalLogPathandfilename' \r\n\r\n--with\u00a0Windows\u00a0Authentication \r\nspCreateDatabase\u00a0'Server',default,default, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'MyDatabaseName', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'MyLogicalDataFileName', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'MyPhysicalDataPathandfilename', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'MyLogicalLogFileName', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'MyPhysicalLogPathandfilename' \r\n\r\n*\/ \r\n\r\nAS \r\nDECLARE\u00a0@hr\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0HRESULT\u00a0returned\u00a0from\u00a0the\u00a0OLE\u00a0operation \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ii\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--a\u00a0simple\u00a0counter \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@iimax\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0max\u00a0of\u00a0the\u00a0iteration \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@objServer\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0Server\u00a0object \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@objDatabase\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0Database\u00a0object \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@objDBfileData\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0Database\u00a0file \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@objLogFile\u00a0INT,\u00a0--the\u00a0log\u00a0file \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ErrorObject\u00a0INT,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0error\u00a0object \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@strErrorMessage\u00a0VARCHAR(255)--the\u00a0potential\u00a0error\u00a0message \r\n\r\n--and\u00a0our\u00a0'constants' \r\nDECLARE\u00a0@SQLDMOGrowth_MB\u00a0INT\u00a0SET\u00a0@SQLDMOGrowth_MB\u00a0=\u00a00 \r\nDECLARE\u00a0@SQLDMOGrowth_Percent\u00a0INT\u00a0SET\u00a0@SQLDMOGrowth_Percent\u00a0=\u00a01 \r\nDECLARE\u00a0@SQLDMOGrowth_Invalid\u00a0INT\u00a0SET\u00a0@SQLDMOGrowth_Invalid\u00a0=\u00a099 \r\n\r\n\r\nSET\u00a0nocount\u00a0ON \r\n\r\nEXEC\u00a0@hr\u00a0=\u00a0sp_OACreate\u00a0'SQLDMO.SQLServer',\u00a0@objServer\u00a0OUT \r\n\r\nIF\u00a0@password\u00a0IS\u00a0NULL\u00a0OR\u00a0@login\u00a0IS\u00a0NULL \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--use\u00a0a\u00a0trusted\u00a0connection\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@hr=0\u00a0SELECT\u00a0@strErrorMessage=\r\n'Setting\u00a0login\u00a0to\u00a0windows\u00a0authentication\u00a0on\u00a0'\r\n+@strServer,@ErrorObject=@objServer \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OASetProperty\u00a0@objServer,\u00a0'LoginSecure',\u00a01 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@hr=0\u00a0SELECT\u00a0@strErrorMessage=\r\n'logging\u00a0in\u00a0to\u00a0the\u00a0requested\u00a0server\r\n using\u00a0windows\u00a0authentication\u00a0on\u00a0'+@strServer \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@login\u00a0IS\u00a0NULL\u00a0AND\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAMethod\u00a0@objServer,\r\n'Connect',\u00a0NULL,\u00a0@strServer\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@login\u00a0IS\u00a0NOT\u00a0NULL\u00a0AND\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAMethod\u00a0@objServer,\r\n'Connect',\u00a0NULL,\u00a0@strServer\u00a0,@Login \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END \r\nELSE \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0@strErrorMessage=\r\n'logging\u00a0in\u00a0to\u00a0the\u00a0requested\u00a0server\r\n using\u00a0SQL\u00a0Server\u00a0authentication\u00a0to\u00a0'+@strServer, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ErrorObject=@objServer \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IF\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAMethod\u00a0@objServer,\r\n'Connect',\u00a0NULL,\u00a0@strServer\u00a0, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@login\u00a0,\u00a0@password \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END \r\n\r\nIF\u00a0@hr=0\u00a0SELECT\u00a0@strErrorMessage='creating\u00a0new\u00a0database'\r\n+@strServer, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0@ErrorObject\u00a0=NULL \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OACreate\u00a0'SQLDMO.Database',\u00a0@objDatabase\u00a0OUT \r\nIF\u00a0@hr=0\u00a0SELECT\u00a0@strErrorMessage='creating\u00a0new\u00a0DBFile'+@strServer \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OACreate\u00a0'SQLDMO.DBFile',\u00a0@objDBfileData\u00a0OUT \r\nIF\u00a0@hr=0\u00a0SELECT\u00a0@strErrorMessage='creating\u00a0new\u00a0Logfile'+@strServer \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OACreate\u00a0'SQLDMO.LogFile',\u00a0@objLogFile\u00a0OUT \r\n\r\nIF\u00a0@hr=0\u00a0SELECT\u00a0@strErrorMessage\u00a0=\u00a0'setting\u00a0the\u00a0Database\u00a0name', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ErrorObject=@objDatabase \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr=\u00a0sp_OASetProperty\u00a0@objDatabase,\u00a0'Name',@DatabaseName \r\n\r\nIF\u00a0@hr=0\u00a0SELECT\u00a0@strErrorMessage\u00a0=\u00a0'setting\u00a0the\u00a0DataFile\u00a0properties', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ErrorObject=@objDBFileData \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr=\u00a0sp_OASetProperty\u00a0@objDBFileData,\u00a0'Name',@DataFileName \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr=\u00a0sp_OASetProperty\u00a0@objDBFileData,\u00a0'PhysicalName', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@DataFilePath \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr=\u00a0sp_OASetProperty\u00a0@objDBFileData,\u00a0'PrimaryFile',1 \r\n--Specify\u00a0file\u00a0growth\u00a0in\u00a0chunks\u00a0of\u00a0fixed\u00a0size\u00a0for\u00a0all\u00a0data\u00a0files. \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr=\u00a0sp_OASetProperty\u00a0@objDBFileData,\u00a0'FileGrowthType', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@SQLDMOGrowth_MB \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr=\u00a0sp_OASetProperty\u00a0@objDBFileData,\u00a0'FileGrowth',1 \r\n\r\nIF\u00a0@hr=0\u00a0SELECT\u00a0@strErrorMessage='using\u00a0method\u00a0to\u00a0add\u00a0'+@DataFileName, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ErrorObject=@objDatabase \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAMethod\u00a0@objDatabase,\r\n'FileGroups(\"PRIMARY\").DBFiles.Add',NULL, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0@objDBFileData \r\n\r\nIF\u00a0@hr=0\u00a0SELECT\u00a0@strErrorMessage='Setting\u00a0the\u00a0logfile\u00a0properties\u00a0of\u00a0'\r\n+@LogFileName, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ErrorObject=@objLogFile \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr=\u00a0sp_OASetProperty\u00a0@objLogFile,\u00a0'Name',@LogFileName \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr=\u00a0sp_OASetProperty\u00a0@objLogFile,\u00a0'PhysicalName', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @LogFilePath \r\nIF\u00a0@hr=0\u00a0SELECT\u00a0@strErrorMessage='using\u00a0method\u00a0to\u00a0add\u00a0' \r\n+@LogFileName, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0@ErrorObject=@objDatabase \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAMethod\u00a0@objDatabase,\r\n'TransactionLog.LogFiles.Add',NULL, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @objLogFile \r\n\r\nIF\u00a0@hr=0\u00a0SELECT\u00a0@strErrorMessage='using\u00a0method\u00a0to\u00a0add\u00a0'\r\n+@DatabaseName+'\u00a0to\u00a0the\u00a0server', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0@ErrorObject=@objServer \r\nIF\u00a0@hr=0\u00a0EXEC\u00a0@hr\u00a0=\u00a0sp_OAMethod\u00a0@objServer,\u00a0'Databases.Add',NULL, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @objDatabase \r\n\r\n--and\u00a0handling\u00a0any\u00a0errors \r\nIF\u00a0@hr&lt;&gt;0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DECLARE\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@Source\u00a0VARCHAR(255), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@Description\u00a0VARCHAR(255), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@Helpfile\u00a0VARCHAR(255), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@HelpID\u00a0INT \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0EXECUTE\u00a0sp_OAGetErrorInfo\u00a0\u00a0@errorObject,\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@source\u00a0output,@Description\u00a0output,\r\n@Helpfile\u00a0output,@HelpID\u00a0output \r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0@strErrorMessage='Error\u00a0whilst\u00a0'\r\n+@strErrorMessage+',\u00a0'+@Description \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0RAISERROR\u00a0(@strErrorMessage,16,1) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END \r\nEXEC\u00a0sp_OADestroy\u00a0@objServer\u00a0\u00a0\u00a0\u00a0\u00a0--the\u00a0Server\u00a0object \r\nEXEC\u00a0sp_OADestroy\u00a0@objDatabase\u00a0\u00a0\u00a0--the\u00a0Database\u00a0object \r\nEXEC\u00a0sp_OADestroy\u00a0@objDBfileData\u00a0--the\u00a0Database\u00a0file \r\nEXEC\u00a0sp_OADestroy\u00a0@objLogFile\u00a0\u00a0\u00a0\u00a0--the\u00a0Log\u00a0file\u00a0object<\/pre>\n<p>Hopefully, this has provided a taster of the power of DMO for automating common SQL server admin tasks. Again, to access the rest of the DMO procedures that accompany this article, just click on the link below:<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/database-administration\/more-database-administration-and-development-automation-using-dmo\/\">ACCESS\u00a0MORE DMO PROCEDURES<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Phil Factor&#8217;s DMO automation procedures provide a valuable additon to any DBAs toolkit.&hellip;<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4519,4170,4520,4518,4150,4151,4254],"coauthors":[6813],"class_list":["post-187","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-automation","tag-database-administration","tag-distributed-management-objects","tag-dmo","tag-sql","tag-sql-server","tag-stored-procedures"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/187","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=187"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/187\/revisions"}],"predecessor-version":[{"id":76765,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/187\/revisions\/76765"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=187"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}