Retrieving Arguments from a Database

Microsoft® Windows® 2000 Scripting Guide

« Previous | Next »   

Databases are especially useful if you have scripts whose arguments might vary each time the script is run. For example, you might have a script that backs up a separate set of computers each day. Although this information can be stored in a text file, you would need to parse the entire text file each time the script ran, picking out the computers of interest. By contrast, you can construct a database query that will retrieve only the computers scheduled to be backed up on a given day. This approach is more efficient than reading through and parsing a text file.

Table 17.1 shows a simple database listing computer names and the day of the week each computer is scheduled for a full backup.

Table 17.1   Sample Backup Schedule Database

ComputerName	BackupDay
Server1	Monday
Server2	Wednesday
Server3	Friday
Server4	Monday

To create a script that runs against the appropriate computers on the appropriate days, you simply include code that limits data retrieval to a specific day. For example, this SQL query returns only the set of computers designated for backup on Thursday:

" SELECT * FROM Computers WHERE BackupDay = 'Thursday'" 

By including additional fields within the table, you can construct an all-purpose database that contains the arguments for many of your scripts. For example, additional fields might indicate the date to back up and clear event logs, the dates and times for performance monitoring, or the list of services to be checked on a routine basis.

Note

• Databases are discussed in more detail later in this chapter.

Scripting Steps

Listing 17.3 contains a script that retrieves arguments from a database. (The database and the database table must exist before this script can run.) To carry out this task, the script must perform the following steps:

1. Create three constants adOpenStatic, adLockOptimistic, and adUseClient and set the value of each to 3.

   These constants will be used to configure the CursorLocation, CursorType, and LockType for the connection.

2. Create an instance of the ADO Connection object (ADODB.Connection).

   The Connection object makes it possible for you to issue queries and other database commands.

3. Create an instance of the ADO Recordset object (ADODB.Recordset).

   The Recordset object stores the data returned from your query.

4. Use the Connection object Open method to open the database with the Data Source Name Inventory.

   Be sure to append a semicolon (;) to the Data Source Name.

5. Set the CursorLocation to 3 (client side) by using the constant adUseClient.
6. Use the Recordset object Open method to retrieve all the records from the ServerList table.

   The Open method requires four parameters:

   • The SQL query ("SELECT * FROM ServerList")
   • The name of the ADO connection being used (objConnection)
   • The cursor type (adOpenStatic)
   • The lock type (adLockOptimistic)
7. Use the MoveFirst method to move to the first record in the recordset.
8. Set the value of the variable strComputer to the value of the ComputerName field in the recordset. The variable strComputer will then represent the name of the first computer that the script must connect to.
9. Use a GetObject call to connect to the WMI namespace root\cimv2 on the remote computer (as specified by strComputer), and set the impersonation level to Impersonate.
10. Use the ExecQuery method to query the Win32_Service class.

    This query returns a collection consisting of all the services installed on the computer.

11. Echo the server name and the number of installed services (determined by using the Count property).
12. Use the MoveNext method to move to the next record in the recordset, and repeat the process until the end of the recordset has been reached.
13. Close the recordset.
14. Close the connection.

Listing 17.3   Retrieving Arguments from a Database

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 

Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3 Set objConnection = CreateObject("ADODB.Connection") Set objRecordset = CreateObject("ADODB.Recordset") objConnection.Open "DSN=Inventory;" objRecordset.CursorLocation = adUseClient objRecordset.Open "SELECT * FROM ServerList" , objConnection, _     adOpenStatic, adLockOptimistic objRecordSet.MoveFirst Do While Not objRecordSet.EOF     strComputer = objRecordSet("ComputerName")     Set objWMIService = GetObject("winmgmts:" _     & "{impersonationLevel=impersonate}!\\" & strComputer& "\root\cimv2")     Set colServices = objWMIService.ExecQuery _         ("SELECT * FROM Win32_Service")     Wscript.Echo strComputer, colServices.Count     objRecordSet.MoveNext Loop objRecordset.Close objConnection.Close

---

 Send us your feedback

« Previous | Next »