Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

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:

  1. Present description screen

  2. Present options(User makes selection)

  3. Prompt for server selection and login

  4. If the control function is server based;

    • Call the server object

    • Return

  5. If the control function is database based;

    • Prompt for the database or object affected

    • Call the database and/or other object

    • Return

  6. (User Exits)

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:

oDatabase.CheckTables(repair type) 

The repair type can be found in this chart:

SQLDMORepair_Allow_DataLoss

Attempt all database repair regardless of the possibility of data loss. For example, delete corrupted text objects.

SQLDMORepair_Fast

Attempt database repair tasks that do not incur data loss.

SQLDMORepair_None

Default. Do not attempt database repair on database inconsistencies encountered.

SQLDMORepair_Rebuild

Attempt database repair tasks that do not incur data loss. Rebuild indexes on successful database repair.


Grant privileges on the database:

oDatabase.Grant(privilege, users)

And here are the constants for the privileges:

SQLDMOPriv_AllDatabasePrivs

Grant all database permissions to the users or roles listed

SQLDMOPriv_CreateDatabase

Grant the execute permission for the CREATE DATABASE statement

SQLDMOPriv_CreateDefault

Grant the execute permission for the CREATE DEFAULT statement

SQLDMOPriv_CreateFunction

Can create and own UserDefinedFunction objects

SQLDMOPriv_CreateProcedure

Can create and own StoredProcedure objects

SQLDMOPriv_CreateRule

Grant the execute permission for the CREATE RULE statement

SQLDMOPriv_CreateTable

Grant the execute permission for the CREATE TABLE statement

SQLDMOPriv_CreateView

Grant the execute permission for the CREATE VIEW statement

SQLDMOPriv_DumpDatabase

Grant permission to back up database

SQLDMOPriv_DumpTable

Maintained for compatibility with previous versions of SQL-DMO

SQLDMOPriv_DumpTransaction

Grant permission to back up the database transaction log


Delete a database called "PubsCopy":

oServer.KillDatabase("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!

To backup the "pubs" database:

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

Restore the "pubs" database:

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.

Online Resources

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.