Doing DBA Chores with SQL-DMO

When I first heard about SQL Distributed Management Objects (SQL-DMO), I thought, Who needs another way to manage databases when I've got T-SQL and Enterprise Manager? But when I needed to manage many servers hosting large, complex databases, I discovered the power of using SQL-DMO objects embedded in Windows Script Host (WSH) scripts. (For more information about SQL-DMO, see Jason Fisher, "SQL-DMO Picks Up Where Enterprise Manager Leaves Off," February 2001.) Let's look at some real-world scripts that use SQL-DMO objects and that can help you manage an enterprise environment where T-SQL scripts and Enterprise Manager aren't enough.

SQL-DMO objects can perform all Enterprise Manager functions and virtually all T-SQL functions. When you wrap SQL-DMO objects in Visual Basic for Applications (VBA), Visual Basic (VB), or either of the WSH languages (VBScript and JScript), they can do jobs—such as performing an iterative action on a large number of objects scattered over many databases and servers—that are beyond the capabilities of Enterprise Manager and T-SQL. Scripts that combine SQL-DMO objects with the rich features that the host language provides are useful when you need to interact with files, registries, networks, and so on.

I used VBScript to write the code in my examples and WSH to execute it. WSH is a technology that ships with Windows 2000 and Windows 98 and lets the OS execute scripts written in either VBScript or JScript. If you don't have WSH on your system, you can download it free from the Microsoft Windows Script Technologies Web site (http://msdn.microsoft.com/scripting). You can also copy these scripts into an ActiveX Script task in a Data Transformation Services (DTS) package, into an Active Server Pages (ASP) page, or into a SQLServerAgent service job step (in SQL Server 2000) with few modifications.

In this article, I examine scripts that a DBA can use to tackle simple but time-consuming jobs. These scripts are quick examples for one-time jobs, but you can modify the scripts for more permanent solutions such as scheduled DTS packages. Good DBAs have a toolbox of past scripts that they can quickly copy and change for any occasion.

Change Owner of All Tables in Database

When multiple users (who aren't sa or in the systems administrator's role) create objects (e.g., tables, views, stored procedures) without explicitly declaring database owner (dbo) as the owner, the user who creates the object owns it. Any other user who references the object must use the fully qualified name, such as fred.FredsTable instead of FredsTable.

The stored procedure sp_changeobjectowner works well for one object at a time, but what if you need to change the ownership of many objects? The script in Listing 1 changes the owner of all Pubs database tables to dbo. The first line creates an instance of the oServer class. The second line sets the oServer object's LoginSecure property to True to allow a Windows authentication connection. For SQL Server authentication, set the LoginSecure property to False (the default). The third line uses the Connect method to connect to the server in the first argument. For SQL Server authentication, the login name is the second argument and the password (in quotes) is the third.

The next line creates a Pubs database object. The For...Each loop iterates through the Tables collection of the Pubs database object and checks to determine whether each table object's Owner property equals dbo. If not, the code sets the property to dbo and outputs the name of the table that the owner changed.

If a user double-clicks a .vbs file in Windows Explorer, by default each Wscript.Echo statement opens a message box that requires acknowledgment. I recommend running .vbs scripts as arguments to the command line's cscript (C:>cscriptMyScript.vbs). After you run the script, Wscript.Echo writes the output to the command-line window. UNIX users will feel at home with this method; you can pipe the output to More or redirect it to a file.

Get Version Information from List of Servers

To apply a script to more than one server, you can feed a list of servers to the script from a text file, as Listing 2 shows. This script's output displays the SQL Server version and service pack level. The first four statements provide error handling and a graceful exit if no argument is present. The next two lines create a SQLServer object and set its LoginSecure property to True. Then, the script creates a FileSystemObject class object, which isn't part of SQL-DMO but demonstrates the flexibility and the ease of integrating SQL-DMO with the native object libraries that VBScript and JScript include.

The object's methods read the server names from a text file, which the script passes as an argument. The Do...Until loop iterates through the server list, connecting to each server and outputting the SQLServer object's VersionString property. At the end, the script destroys all objects (by setting them equal to Nothing) that it instantiated, releasing the objects' resources back to the OS. Usually, a script automatically destroys objects when the script exits, but explicitly releasing the resources is good practice. To run Listing 2's script, use C:>cscript GetVersionInfo.vbs SqlServers.txt at the command line.

Change Data Type from Char to Nchar

To change data types through SQL-DMO, let's examine a script that loops through nested collections (each column in each user table in each database) on the servers listed in a text file. The script in Listing 3 runs only on SQL Server 2000 (earlier releases of SQL Server don't let you change data types through SQL-DMO). The script changes all char columns to nchar by using the Column object's AlterDataType method. The first few script lines use a Yes or No message box, asking whether the user wants to proceed. Sometimes, I put a message box prompt at the beginning of a script that make major modifications to prevent users from unintentionally running the script by double-clicking it in Windows Explorer. However, if a DTS ActiveX Script task or a SQLServerAgent job will use your script, don't include any message boxes or other code that interacts with the user.

The On Error Resume Next statement continues execution after the script encounters an error. This script could trigger an error if it tries to change the data type of a column that a foreign key constraint references or if the changed column is part of an index key. After calling the AlterDataType method, the script checks the Err object's Count property. If Err.Count is greater than 0, the script prints the Err.Description property and clears the Err object. Running the script shows you unsuccessful data-type changes and says why they failed.

Import Data from File to Table

The script in Listing 4 loops through a folder's files and uses the bulk copy program (bcp) to import .dat files. If the bcp is successful, the script moves the file and writes the number of records that it imported to a log file. If an error occurs, the script writes the error to an error file. This kind of script is useful for monitoring an FTP root folder and processing incoming data files. You can easily modify this script to perform other steps, such as examining a data file's contents to determine which table should receive the bcp data. Or, after the script has bulk-copied the data, you can initiate further SQL Server processing.

To set up the environment to run this script, you create a data file in the C:temp folder. (If you use another folder, make sure you set your script's sFolder variable to that folder.) Assuming that you have a locally installed SQL Server machine containing the Pubs database, run the following bcp command (providing your server's sa password).

bcp pubs.dbo.jobs out c:tempImportData.dat -c -Usa -Ppassword

Then, create the target table in the Pubs database, as Listing 5 shows.

The first statement in Listing 4 sets sFolder to point to the folder that contains the data and log files. Then, the script creates a FileSystemObject object called fso. The script looks for an ImportedFiles folder, to which the script will move files after it has bulk-copied them. If the ImportedFiles folder doesn't exist, fso creates it. Next, the script creates a file object that will have the imported-rows count appended to it. The script then creates a SQLServer object, a Pubs database Database object, and an ImportData table object (to receive the bcp data). Next, the script creates a BulkCopy object and sets that object's DataFileType property to 2 (tab-delimited) and the ErrorFilePath property to the error file's full pathname.

The script then creates a RegExp object in which to pattern-match strings by using the regular expression that the object's Pattern property set. RegExp, like FileSystemObject, isn't in the SQL-DMO object library. When RegExp's Execute method runs, it matches only those files that end with a .dat extension, creating an oMatches object. If oMatches.Count is greater than 0, the script sets the BulkCopy object's DataFilePath to the full path of the file that the script imports. The next line uses the BulkCopy object as an argument when the script calls the Table object's ImportData method. The variable iRecordsImported captures the value that the ImportData method returns so that the script can write that value to the log file with a timestamp, which the now() function provides. The script then moves the data file to the ImportedFiles folder.

DBAs need all the tricks and tools they can find to perform their duties. Using SQL-DMO objects in VBScript files should be an important part of every DBA's toolkit. (More information about SQL-DMO objects.)