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: Database Information
Last updated Mar 28, 2003.
We're up to the point in this series of SQL-DMO programming articles where we can talk about creating useful code. In this article, I introduce VBScript file system operators and show you how to send information about a database to a log.
Let's begin by talking a bit about working with files. Earlier, I explained that the SQL-DMO library is really a .DLL file that has objects within it. Parent objects, we learned, have various properties and methods. Some of these "parent" objects (such as SQLServer) have "child" objects (such as Databases).
To work with files, we need to invoke the parent object for them, called Scripting. This isn't the only route to working with files; as a matter of fact, it's a fairly new one.
You might run into references that point out some security implications for the Scripting object. If you're patched properly, however, and follow recommended security guidelines, you should have no problem with this object.
The Scripting parent object gives you easy access to the FileSystem child object, which allows you to work with files and directories. The FileSystem parent object, in turn, contains various operations that can create a child File object.
Not only can the FileSystem object create files, it can also read them, check to see if they exist, delete them, and so on. You can also work with drives using FileSystem. (A couple of references at the end of the article show you more about this useful object.)
But the file system isn't what we're after it's just a means to an end. What we want to do is send the information about a database to somewhere a bit more permanent than a message box. Once you learn this technique, you can extend the concept into other applications.
I'll begin by outlining what I want the code to do, write the comments, and then code the comments.
First the outline. Here's what I came up with:
Enter a server name to work with
Connect to the server
Access the "pubs" database
Get several bits of information about the database
Data Space Used
Index Space Used
Auto Create Stats
Auto Update Stats
Null Column Inserts
DBO Use Only
Trunc. on Ckpt.
Select Into/bulk copy
Last Log Backup
Create a log file
Overwrite if it exists
Show the data on screen as well
Notice that my requirements are the same database every time: pubs. You might want more flexibility in the database choice. The same holds true for the log file. To make those two hard-coded values into user choices, set up a variable for each, and then prompt the user for the information. Then you can use the return values in the proper locations.
With the outline complete, I'll write my comments and then code them:
' dmoDatabaseInfoToFile.vbs ' Created: 12/27/04 - Buck Woody ' Change Log: ' Variable to hold Server Name Dim ServerNameVar ' Variables for the text boxes Dim ServerDisplayVar Dim MsgTxtVar ' Variables for objects Dim oServer Dim oDatabase Dim oFileSystem Dim oFile Dim oMessageBox ' Ask for the name ServerNameVar = InputBox("Enter Server Name:") If ServerNameVar = "" Then ServerNameVar = "(local)" End If ' Create the server object using SQL-DMO Set oServer = CreateObject("SQLDMO.SQLServer2") ' Login with current Windows account oServer.LoginSecure = True oServer.Connect ServerNameVar ' Get a Database to work with - you could ask the user for one Set oDatabase = oServer.Databases("pubs") ' Fill out the info you want MsgTxtVar = "Name: " & oDatabase.name & VBCrLf _ & "Create Date: " & oDatabase.CreateDate & VBCrLf _ & "Owner: " & oDatabase.Owner & VBCrLf _ & "Version: " & oDatabase.Version & VBCrLf _ & "Status: " & oDatabase.Status & VBCrLf _ & "File Path: " & oDatabase.PrimaryFilePath & VBCrLf _ & "Total Size: " & oDatabase.Size & " MB" & VBCrLf _ & "Data Space Used: " & oDatabase.DataSpaceUsage & " MB" & VBCrLf _ & "Index Space Used: " & oDatabase.IndexSpaceUsage & " MB" & VBCrLf _ & "Space Available: " & oDatabase.SpaceAvailableInMB & " MB" & VBCrLf _ & "Auto Close: " & oDatabase.DBOption.AutoClose & VBCrLf _ & "Auto Create Stats: " & oDatabase.DBOption.AutoCreateStat & VBCrLf _ & "Auto Shrink: " & oDatabase.DBOption.AutoShrink & VBCrLf _ & "Auto Update Stats: " & oDatabase.DBOption.AutoUpdateStat & VBCrLf _ & "Null Column Inserts: " & oDatabase.DBOption.ColumnsNullByDefault & VBCrLf _ & "Null Compares: " & oDatabase.DBOption.CompareNull & VBCrLf _ & "DBO Use Only: " & oDatabase.DBOption.DBOUseOnly & VBCrLf _ & "Trunc. on Ckpt.: " & oDatabase.DBOption.TruncateLogOnCheckpoint & VBCrLf _ & "Select Into/bulk copy: " & oDatabase.DBOption.SelectIntoBulkCopy & VBCrLf _ & "Last Log Backup: " & oDatabase.TransactionLog.LastBackup ' Create File System Object Set oFileSystem = CreateObject("Scripting.FileSystemObject") ' Create a file with the Database name embedded Set oFile = oFileSystem.CreateTextFile("C:\temp\Info.log") ' Write the Database Data oFile.Write(MsgTxtVar) oFile.Close 'Show the file oMessageBox = MsgBox(MsgTxtVar,VBOK,"Log file contents for " & oDatabase.Name) ' Clean up Set oFilesystem = Nothing set oDatabase = Nothing Set oServer = Nothing
Many of these variables are part of the Database child object, but a few (such as oDatabase.DBOption.SelectIntoBulkCopy) use child objects of Database.
The code writes a log file out to the temp directory. If you're running this code on your test system, make sure you have that directory first.
You can extend this code in many ways. I've used this kind of code with an Append operation to track the growth of a database over time. I run the code each day, pipe just the size results to a file, and then (once a month) I bring it into a spreadsheet. From there, I graph the data, and do estimations on trends.
Another extension on this type of code is to read the file rather than create one. The file can contain directives to perform a backup or some other maintenance. Your program runs with your privileges, and the users can create a file that you read to see if they want to perform a backup or some other action. In this way, you can allow them to perform only certain operations even if they don't have access to the database. The possibilities are endless.
In my next article, I'll show you how to use SQL-DMO to perform various operations on the database. See you then!
The full documentation for the SQL Server Object Library can be found here.
You can bundle up your source code so that it runs and others can't read it. Microsoft has the details here.