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 Control
Last updated Mar 28, 2003.
It's all about control. Technical professionals, especially database administrators, are especially fond of having control, at least when it comes to the systems they administer. In this case, it's not a bad thing to be a control freak. After all, that's why they pay us.
In this installment of our series on SQL Data Management Object (SQL-DMO) programming, I change the rules a bit. Instead of a complete code segment, I'll show you code snippets that perform various controlling acts on servers, databases, and objects. The reason for the change is that scripting doesn't provide an adequate user interface for what we need. For multiple selections that might require more interaction with the user, you need a complex interface with multiple selections (radio buttons), inputs (text boxes), and pop-up or follow-on forms. I still use scripting in the snippets, though, since the code for that works (for the most part) whether you use a full language or VBScript.
You could always use scripting in your Active Server Pages or other Web interfaces. However, since we already have a full coding section here at InformIT, I thought it best to lay out a few of the more prevalent control functions as blocks which you can incorporate into your code of choice. You could also just make individual scripts for each function.
Another option might be to make an input box and capture the desired function as a variable. In that case, you'd take that variable (a number, letter or full word) and use IF - END IF statements to spawn off the code you wanted. This is messy, however, and before you do that I'd recommend creating at least a Webbased interface with richer controls.
In any case, the process is the same. Remember that the process I advocate is to create an outline of the code, create comments based on the outline, and then code the comments. Given that process, here's the general outline:
Present description screen
Present options(User makes selection)
Prompt for server selection and login
If the control function is server based;
Call the server object
If the control function is database based;
Prompt for the database or object affected
Call the database and/or other object
There's actually quite a bit going on here, and this outline introduces a few design decisions. For instance, do you want to have all the information required on the main screen at the beginning of the program? This is called the "more is more" approach. Or perhaps you want to request user input only when it is needed, like you might see in a wizard. This is called the "less is more" approach, and normally involves more screens or tabs.
There is no pat answer; as each program's goal and methodology will depend on the user audience. The "less is more" approach is more difficult to implement using scripting.
You'll also need to anticipate things that might go wrong in the program, such as the user typing an incorrect server name. You would then "trap" that error and properly handle it rather than having it crash the program. Even better, you could "sniff out" the database servers and present a pre-set list to the user. That would alleviate any typing errors.
You should create your own outline, based on what you want the program to do. Once you have that outline, make the decision about whether you want the "less is more" or "more is more" interface, and how you want to handle the errors. Now rearrange your outline to reflect these parameters.
Next, create comments for the code blocks that will implement the design. The comments might not follow the same order as the outline; but that's normal.
So without further ado, here are a few code blocks that provide control functions for your database. This code depends on the SQL-DMO library, and needs a SQL-Server object to work with. You can reference the previous articles to find a sample application, but here's the basic connection block:
Set oServer = CreateObject("SQLDMO.SQLServer2") oServer.LoginSecure = True oServer.Connect "(local)"
This sets a connection to the local system. Your code might prompt the user for the server name, as I showed you in previous articles. You also need a database to work with, so here's the base code for that:
Set oDatabase = oServer.Databases("pubs")
Given those parameters, here are a few of the more popular control functions:
Perform a table check on the database:
The repair type can be found in this chart:
Attempt all database repair regardless of the possibility of data loss. For example, delete corrupted text objects.
Attempt database repair tasks that do not incur data loss.
Default. Do not attempt database repair on database inconsistencies encountered.
Attempt database repair tasks that do not incur data loss. Rebuild indexes on successful database repair.
Grant privileges on the database:
And here are the constants for the privileges:
Grant all database permissions to the users or roles listed
Grant the execute permission for the CREATE DATABASE statement
Grant the execute permission for the CREATE DEFAULT statement
Can create and own UserDefinedFunction objects
Can create and own StoredProcedure objects
Grant the execute permission for the CREATE RULE statement
Grant the execute permission for the CREATE TABLE statement
Grant the execute permission for the CREATE VIEW statement
Grant permission to back up database
Maintained for compatibility with previous versions of SQL-DMO
Grant permission to back up the database transaction log
Delete a database called "PubsCopy":
Be careful here; you won't be prompted for a confirmation. Make sure you give your users a chance to bail out of this function in case they click it by accident!
First, you need to create another variable, this one for the backup object. You can put this at the top, where you created the other variables.
Dim oBackup As New SQLDMO.Backup
And then the following lines will then backup the pubs database to a backup device named "pubsBackupDev":
oBackup.Action = SQLDMOBackup_Database oBackup.Database = "pubs" oBackup.Devices = "pubsBackupDev" oBackup.SQLBackup oSQLServer
For this, you need to create another variable again, just like in the backup.
Dim oRestore As New SQLDMO.Restore
You use that variable as an object and start the restore process. In this case, I restore from the same device to which I backed up, and I also overwrite the database if it exists. Use with care!
oRestore.Action = SQLDMORestore_Database oRestore.Database = "pubs" oRestore.Devices = "pubsBackupDev" oRestore.ReplaceDatabase = True oRestore.SQLRestore oSQLServer
There are many other methods for database control that I'll show you in future articles. One of the most useful is the scripting method that you can use to script out any or all objects in the database.
The full documentation for the SQL Server Object Library can be found here.
InformIT Tutorials and Sample Chapters
If you're interested in learning more about programming, you should start with object-orientation. There's a great sample chapter called Software Architecture: Basic Training By Raphael Malveau and Thomas J. Mowbray that you can read for free.