Hey, Scripting Guy! How can I save a disconnected recordset?

— RW

Hey, RW. You know, the Scripting Guys believe in keeping things as simple as possible. For example, when one of the Scripting Guys had to buy a new tie several years ago, he simply asked his dad to tie it for him; on the rare occasions when this Scripting Guy has to wear a tie he carefully removes the thing at the end of the day and – leaving it knotted – hangs it up and waits for the next time he needs to dress up. Granted, there are probably other ways to deal with this (like, say, maybe learning how to tie his own tie), but this was definitely the simplest way to handle the situation, so that was the path he took.

We took the same approach when it came time to answer your question. There are several ways we could save a disconnected recordset: we could open an existing database and copy the records there; we could use the FileSystemObject to save the data to a text file; we could use the Excel object model and write the information to a spreadsheet. Despite all those wonderful possibilities, though, we decided to keep things as simple and possible and save the disconnected recordset to an XML file, an approach requiring just two lines of code. As we noted, there are other ways to deal with this, but this was definitely the simplest way to handle the situation, so this was the path we took.

One quick note: we aren’t going to talk about disconnected recordsets in today’s column, nor are we going to explain the code for creating a disconnected recordset; we simply don’t have room to do all that. For now, simply understand that a disconnected recordset is like a database table that exists only in memory; it’s considered “disconnected” because it isn’t tied to any real, live database. Disconnected recordsets can be very useful to script writers; for one thing, you can store WMI data into a disconnected recordset and then sort that data on any property value you wish. That’s nice; as you well know, when it comes to WMI data you’re usually at the mercy of whatever sort order WMI happens to use.

Here’s a sample script that gets a list of all the services running on a computer and then stores the name and state of each of those services in a disconnected recordset. After we’ve created and populated our recordset we then use the Save method to save the data to an XML file:

Const adPersistXML = 1
Const adVarChar = 200
Const MaxCharacters = 255
Const adFldIsNullable = 32

Set DataList = CreateObject(“ADOR.Recordset”)
DataList.Fields.Append “ServiceName”, adVarChar, MaxCharacters, adFldIsNullable
DataList.Fields.Append “State”, adVarChar, MaxCharacters, adFldIsNullable
DataList.Open
strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer& “\root\cimv2”)
Set colItems = objWMIService.ExecQuery(“SELECT * FROM Win32_Service”)
For Each objItem in colItems
    DataList.AddNew
    DataList(“ServiceName”) = objItem.Name
    DataList(“State”) = objItem.State
    DataList.Update
Next
DataList.Sort = “ServiceName”
DataList.Save “C:\Scripts\Test.xml”, adPersistXML

Don’t worry too much about the size of the script; most of the code here involves creating and populating the disconnected recordset. We’re really interested in only two lines of code: the first line and the last line. In line 1, we define a constant named adPersistXML and set the value to 1; this tells the script that we want to save the data in XML format:

Const adPersistXML = 1

In the last line of the script we then call the Save method and save the data:

DataList.Save “C:\Scripts\Test.xml”, adPersistXML

As you can see, we pass Save a pair of parameters: the complete path to the new XML file, and the constant adPersistXML. It’s that easy.

Of course, we know what you’re thinking: what the heck am I supposed to do with an XML file? Well, here’s one suggestion: use ADO to later reopen that file. Here’s a very simple script that opens the file Test.xml and then echoes back the contents:

Set DataList = CreateObject(“ADOR.Recordset”)
DataList.Open “C:\Scripts\Test.xml”

DataList.MoveFirst
Do Until DataList.EOF
    Wscript.Echo DataList.Fields.Item(“ServiceName”) _
        & ” – ” & DataList.Fields.Item(“State”)
    DataList.MoveNext
Loop

Easy, huh? You create an instance of the ADOR.Recordset object and then call the Open method, passing along the path to the XML file you want to open.

With any luck this should take care of your problem, RW; if not please let us know. After all, we’re well aware that sometimes things can go wrong even when you do take the simplest possible route. For example, through a tragic mix-up our Scripting Guy’s tie was recently sent to the cleaners. It came back nice and clean; it also came back untied. Now he has to hope none of his friends or relatives get married or win any prestigious awards until he has had a chance to go back home and get his dad to re-tie his tie for him. (Although, all things considered, that still seems way simpler than trying to learn how to tie a tie.)