Diwali Deal : Flat 20% off + 2 free self-paced courses + $200 Voucher  - SCHEDULE CALL

sddsfsf

All About SQL Server DMOs

 

SQL Server can be managed programmatically using system-stored procedures, but Distributed Management Objects (DMOs) offer a more modern, object-oriented alternative. This article introduces SQL DMO in SQL Server 7.0 and SQL Server 2000, describes the SQL-DMO object model, and then focuses primarily on the database structure and the Job Server object model structure. The sample code and article demonstrate how to use various objects, such as the registry object, the configuration object, and the database object, to automate everyday administrative tasks such as scheduling and backups.

What is SQL Distributed Management Objects?

SQL-DMO (Distributed Management Objects) is a robust set of objects that grants programmatic access to the administrative and development components of SQL Server to developers and database administrators. DMO connects to a SQL Server instance via the MS SQL Server ODBC driver, and numerous SQL Server-installed stored procedures support DMO's functionality (and are required for Enterprise Manager to function). On a typical SQL Server installation, these SPS are installed using a master script called "sqldmo.sql." This script is in the "C: Program Files Microsoft SQL Server MSSQL Install" directory.

The script can be rerun to replace broken or missing procedures if necessary. You can execute various database administration and development tasks through the exposed properties and methods and access DMO from a COM development environment like Visual Basic or C++. The examples and references I use here will demonstrate that VB is my preferred development tool. If your development machine does not have an instance of SQL Server running, you may need the components necessary to access the SQL server through DMO. If you're developing in C++, you'll need the appropriate header files, but Microsoft was kind enough to package everything up in a single DLL with the appropriate name "Sqldmo.dll." Although I've never had to register the DLL manually, I anticipate that copying it to your development machine, registering it, setting a reference to the "Microsoft SQLDMO Object Library," and getting started coding should be reasonably straightforward.

Classification of Objects in SQL Server DMO

All the objects in the SQL-DMO hierarchy can be broadly classified into three groups: individual objects (such as databases, Tables, and Columns), collections (such as Databases, Tables, and Columns), and lists. List objects are similar to collections, except they don't support adding and removing elements. Typically you get a list as a return value from a method or property. Data is an integral part of businesses, and there’s great demand for administrators. Therefore, go through the SQL DBA career path if you want to set yourself up for this role.

Working with SQL-DMO Objects

All the objects in the DMO SQL Server hierarchy can be broadly classified into individual objects, collections, and lists. List objects are similar to collections, except they don't support adding and removing elements. Typically, SQL Server developers get a list as a return value from a method or property. For example, the following code fills a combo box control with server names.

Dim sqlApp As New SQLDMO.Application
    Dim NL As SQLDMO.NameList
    Set NL = sqlApp.ListAvailableSQLServers
    For index = 1 To NL. Count
        cboServers.AddItem NL.Item(index)
  Next 

The object model is similar to the hierarchy in Enterprise Manager. Underneath each server is a collection of databases. Tables, Stored Procedures, and Views all exist in collections under a database.

Here is an example of some code that would loop through the table's 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 everyday use for SQL-DMO, which is undoubtedly requested in our forums regularly, 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. We can set several scripting options for this method; for example, we can choose not to include any or only have constraints. 

Some code 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()

This article would only be complete with 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, 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, 'login secure,' '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.'

How to install SQL DMO

All required SQL-DMO components are installed as part of an instance of Microsoft Sql Server or client. SQL-DMO is implemented in a single dynamic-link library (DLL). You may develop SQL-DMO applications on either a client or a server. No additional files are required when using an OLE Automation controller as a development platform. Application development using C and C++ requires the SQL-DMO header files.

Advantages of SQL DMO

The advantages of SQL DMO are:

  1. It facilitates quick problem-solving turnaround from concept to implementation,
  2. Little or no T-SQL required.  

cta14 icon

SQL Testing Training

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Conclusion

SQL Server uses distributed management objects(DMO) for a modern, object-oriented alternative for programmatic administration. In the above writeup, we have discussed SQL Server DMOs. We have discussed its uses, advantages, and how to install distributed management objects in SQL Server.  

Trending Courses

Cyber Security icon

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security icon1

Upcoming Class

2 days 02 Nov 2024

QA icon

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA icon1

Upcoming Class

12 days 12 Nov 2024

Salesforce icon

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce icon1

Upcoming Class

0 day 31 Oct 2024

Business Analyst icon

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst icon1

Upcoming Class

1 day 01 Nov 2024

MS SQL Server icon

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server icon1

Upcoming Class

1 day 01 Nov 2024

Data Science icon

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science icon1

Upcoming Class

2 days 02 Nov 2024

DevOps icon

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps icon1

Upcoming Class

7 days 07 Nov 2024

Hadoop icon

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop icon1

Upcoming Class

8 days 08 Nov 2024

Python icon

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python icon1

Upcoming Class

9 days 09 Nov 2024

Artificial Intelligence icon

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence icon1

Upcoming Class

2 days 02 Nov 2024

Machine Learning icon

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning icon1

Upcoming Class

15 days 15 Nov 2024

 Tableau icon

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau icon1

Upcoming Class

8 days 08 Nov 2024