Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Data Management Objects: Other Database Objects
Last updated Mar 28, 2003.
We've covered quite a bit of ground in the DMO articles I've posted here in the last few weeks. I've explained how to work with many database objects, and in the last few articles I've spent some time showing you how to work with Transact-SQL statements.
In this article, I continue the tour, with a few more pointers on working with other objects in the database. I explain how to use these objects, and how you can put them in any of the code frameworks I've covered previously.
You're probably familiar with that framework by now: You create a new SQL DMO server and any objects you're interested in, connect to them, work with them and then close them out. You also know that certain object groups are called "collections," (such as SQL Servers), and others are the base objects that you can work with (such as a particular table). Having these collections and discrete objects available in code makes DMO programming very powerful. In fact, you can do just about anything to your server and its objects using SQL Server DMO programming. You can, but that doesn't mean you should.
What I mean is that certain activities are better suited to DMO programming than are others. For instance, SQL DMO is very easy to code for managing servers and documenting information, but it's less friendly for creating new database objects. I've focused this series of articles on the more practical applications of DMO code, such as gathering object information, performing maintenance, and running and logging T-SQL queries. I believe these types of activities are worth the time of learning SQL DMO programming.
I'll put these examples into a few categories where I think DMO shines. First, there's object documentation. As I showed you in the first few articles, you can read many properties of the server and its objects. Sure, you can get the same information using T-SQL, but DMO allows you to connect to multiple servers quickly, and allows access to the file system. This allows you to document many servers in one click.
Next, there are the maintenance functions. SQL DMO gives you the ability to update statistics, perform database backups and more. Once again, you can connect to multiple servers and document the process along the way.
Another category of useful DMO programming is running T-SQL statements from a "feeder" file and creating output files.
In the next few weeks, I'll show you two more categories where DMO is very useful: security and scripting.
In this article, let's talk about a few more of the objects you can use in many of these categories. Since they didn't fit neatly into one single category, I thought it best to cover them here, in the middle.
Let's take a look at that basic framework once again:
Set oServer = CreateObject("SQLDMO.SQLServer") oServer.LoginSecure = True oServer.Connect "(local)" ' Get and work with objects here Set oServer = Nothing
The other day, I needed to determine if I had the custom data type defined in all my databases, so I created some code that uses a collection on the database to list them:
Set oUDT = CreateObject("SQLDMO.UserDefinedDatatype") Set oServer = CreateObject("SQLDMO.SQLServer") oServer.LoginSecure = True oServer.Connect "(local)" For Each oUDT in oServer.Databases("pubs").UserDefinedDataTypes strMsgText = strMsgText & oDT & VBCRLF Next MsgBox strMsgText Set oUDT = Nothing Set oServer = Nothing
You can use the same process to find triggers, constraints, and anything that requires you to hop around to find in other tools. By using a server and database collection above the database collection, you can also iterate through all your servers and databases to make sure that you've kept a consistent naming scheme on various objects.
That's really the crux of working with DMO. I've had some comments from other DBAs who ask me, "I can do all this with T-SQL. What's the point?" That's a fair question.
The answer is in the ability to deliver an application that can iterate through the servers and databases and create a consolidated method to collect that information. To be sure, you can do much the same in T-SQL, but that requires T-SQL knowledge and a client program (such as osql) for the user. With a DMO program, you can deliver a simple, consistent, graphical interface to anyone.
Let's look at another example. Along with information about customizations I made to a database, I often need to check up on the automation I put into place. SQL Server provides two basic methods to do that: jobs and alerts. To find out about them, use a property of the Server object that controls the job behavior in SQL Server, called (appropriately enough) JobServer. Using the For Each iteration method, here's some code that gets the names of the all of the jobs in a SQL Server:
Dim oJob Set oServer = CreateObject("SQLDMO.SQLServer") oServer.LoginSecure = True oServer.Connect "(local)" For Each oJob in oServer.JobServer.Jobs strMsgText = strMsgText & oJob.Name & VBCRLF Next MsgBox strMsgText Set oServer = Nothing
Once you have that object, there's a lot more you can do with it. See the full documentation I reference at the end of this article for more properties and methods on the jobs object.
For some properties, such as schedules, you need to reference a particular job, like this:
The alerts object uses the JobServer property as well:
Dim oAlert Set oServer = CreateObject("SQLDMO.SQLServer") oServer.LoginSecure = True oServer.Connect "(local)" For Each oAlert in oServer.JobServer.Alerts strMsgText = strMsgText & oAlert.Name & VBCRLF Next MsgBox strMsgText Set oServer = Nothing
SQL DMO provides access to just about any object you need to control or document on your server.
In the next two articles, I'll finish up this series on SQL DMO programming with two very useful examples: security and scripting.
The full documentation for the SQL Server Object Library can be found here.
You can learn more about SQL Server options over at Windows IT Pro – the full article is here.