Introduction to SQL-DMOBy Damian Maclennen on 18 August 2002 | Tags: DMO Most SQL Server administrative tasks are programmable thanks to a set of objects known as SQL-DMO. This article introduces the concepts of programming DMO, and steps you through some basic, and commonly requested examples. Distributed Management Objects (DMO) is a set of programmable objects that come with SQL Server that make it easy to programatically administer your databases. SQL-DMO is actually the foundation of Enterprise Manager, so you can pretty much do anything programatically that you can do in the management tools. Some of these tasks include : ... and much more. The fun happens inside sqldmo.dll which sits in the \Tools\Binn directory under your Microsoft SQL Server installation. As it is a COM object, you can use it in any COM friendly platform. This includes C++, VB, WSH, ASP, Delphi and of course .NET. For the purposes of this article, I am going to give examples in Visual Basic 6. These examples will work with very little modification in VB Script, including ASP. The first step is to connect to a database server. You can do this using SQL authentication, or use the built in NT authentication depending on your application. Connecting using SQL AuthenticationDim objDMO as SQLServer Set objDMO = new SQLDMO.SQLServer objDMO.Connect "(local)", "sa", "of_course_I_changed_my_password" Connecting using NT AuthenticationThis is as simple as setting the "loginsecure" property to True and leaving off the login and password. Dim objDMO as SQLServer Set objDMO = new SQLDMO.SQLServer objDMO.loginsecure = true objDMO.Connect "(local)" Don't forget at the end of this you will want to disconnect and clean up your objects with a simple : objDMO.DisConnect Set objDMO = nothing The Tables CollectionThe object model within DMO is very similar to the heirachy in Enterprise Manager. Underneath each server is a collection of databases. Each database has collections of objects. Tables, Stored Procedures and Views all exist in collections under a database. Here is an example of some code that would loop through the tables collection to return the name of each table. 'Assuming, we have set up our connection. Dim objDB As Database Set objDB = objDMO.Databases("northwind") Dim oTable As Table For Each oTable In objDB.Tables MsgBox oTable.Name Next Another common use for SQL-DMO, and certainly one that gets requested in our forums on a regular basis, is scripting database objects. Scripting is quite simple. Once we have a table object, as seen in the above example, we can call the script() method and we are returned a string that contains the Create Table script for that object. There are a number of scripting options that we can set for this method, for example we can choose not to include any constraints, or only have contstraints, etc, etc, etc. But I will let you look these up for yourself in Books Online. Here is some code that will generate a script for the employees table in the Northwind database. 'Assuming, we have set up our connection. Dim objDB As Database Set objDB = objDMO.Databases("northwind") Dim oTable As Table Set oTable = objDB.Tables("employees") 'Assuming we have a text box control named text1 Text1.Text = oTable.Script() Now, this article would not be complete without a bit of SQL code. Knowing that we can call COM objects in our T-SQL scripts using the sp_OACreate procedure, it makes sense to put these two concepts to use. Without going into the details of how this works (click the above link for some related articles), a basic script to call DMO from T-SQL is : declare @objDMO int declare @objDatabase int declare @resultCode int declare @dbname varchar(200) declare @tablename varchar(200) declare @cmd varchar(300) declare @temp varchar(8000) Set @dbname = 'PUBS' Set @tablename = 'Authors' EXEC @resultcode = sp_OACreate 'SQLDMO.SQLServer', @objDMO OUT if @resultcode = 0 print 'Created Object' Exec @resultcode = sp_OASetProperty @objDMO, 'loginsecure', 'true' EXEC @resultcode = sp_OAMethod @objDMO, 'Connect', NULL, '(local)' if @resultcode = 0 print 'connected' Set @cmd = 'databases("' + @dbname + '").tables("' + @tablename + '").script' Exec @resultcode = sp_OAMethod @objDMO, @cmd , @temp OUTPUT, 4 print @temp EXEC @resultcode = sp_OADestroy @objDMO if @resultcode = 0 Print 'destroyed object' ConclusionThis has been a very brief introduction to SQL-DMO. It is a topic that a huge book could be written on so I have barely scratched the surface. The examples here are very basic and not very useful in themselves but give you an idea as to what can be achieved. I urge you to experiment with DMO and see what you can do with it. If you have any questions, post them in the forums here and I am sure you will get some help. For more information on SQL-DMO, look in Books Online, and do a search on MSDN. There is heaps of good stuff there. Good luck with it!
|
- Advertisement - |