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:\temp\ImportData.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. (For more information about SQL-DMO objects, see SQL Server Books Online—BOL.)
End of Article
Prev. page
1
[2]
next page -->
