I want to use some data from my company's database server in the administrative scripts that I'm writing. The database is a Microsoft SQL Server system. Can a script obtain data from a SQL Server database?
You can definitely use scripting to access the data in a SQL Server database. You can accomplish the task in several ways, including using VBScript, JScript, or Perl with the Win32:: ODBC extension. I describe how to use ADO from a VBScript script to access a SQL Server database, read the data from a table, and write that data to a file.
Before attempting to run this script, you need to be sure to have Microsoft Data Access Components (MDAC) installed on your system. MDAC provides the middleware support to connect your system to the SQL Server database. MDAC comes on Windows XP and Windows 2000 systems, but you'll need to install it on Windows NT and Windows 9x systems. You can download MDAC from http://www.microsoft.com/data.
The first line of code in the GetDBData.vbs script, which Listing 3 shows, turns on Option Explicit, which requires that all variables be declared. This requirement reduces your chance of making coding errors. The next few lines of code declare the variables that you'll use in the script. Next, in the Set the database connection information section of Listing 3, you set the database and logon information. The sServer, sLogin, and sPwd values need to match similar values on the SQL Server system that you're connecting to. In the Create the ADO Connection and Recordset objects section of Listing 3, the CreateObject function creates an ADO Connection object and an ADO Recordset object. The ADO Connection object connects to the database, and the Recordset object retrieves data from the database. You use the CreateObject function again in the next section of the listing to create a FileSystemObject named oFso, which the system will use to write data to the disk.
After you create all the required objects, you need to set the ConnectionString property of the ADO Connection object to an OLE DB connection string that contains the login information necessary for connection to the database. Then, you need to use the oRs.Open line to open the oRs Recordset object. The first parameter is a SQL Select statement that retrieves two columns from the authors table in the SQL Server pubs database that this example uses. The second parameter links the Recordset object to an active Connection object.
After the Recordset is filled, the FileSystemObject's CreateTextFile method creates a new text file named datafile.txt. Then, within the While loop, a MoveNext method reads all the rows in the ADO Recordset object. As each row is read, a For...Next loop parses each data row. Within the For...Next loop, the script extracts the data for each column by accessing the Value property of each ADO Field object. The script uses that data to build a string in which commas separate each data value. After the script extracts all column data and builds the string, the FileSystemObject's WriteLine method writes the data to the datafile.txt file.
After all rows have been read and the data has been written to a disk, the script closes the oFso FileSystemObject, the oRs Recordset object, and the oCn Connection object. You can use the resulting database information in subsequent scripts or processes.