{"id":67563,"date":"2006-04-11T16:06:00","date_gmt":"2006-04-11T16:06:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/04\/11\/how-can-i-save-a-disconnected-recordset\/"},"modified":"2006-04-11T16:06:00","modified_gmt":"2006-04-11T16:06:00","slug":"how-can-i-save-a-disconnected-recordset","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-save-a-disconnected-recordset\/","title":{"rendered":"How Can I Save a Disconnected Recordset?"},"content":{"rendered":"

 <\/P>\"Hey, \n

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

— RW<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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.<\/P>\n

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. <\/P>\n

One quick note: we aren\u2019t going to talk about disconnected recordsets in today\u2019s column, nor are we going to explain the code for creating a disconnected recordset; we simply don\u2019t 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\u2019s considered \u201cdisconnected\u201d because it isn\u2019t 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\u2019s nice; as you well know, when it comes to WMI data you\u2019re usually at the mercy of whatever sort order WMI happens to use.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. So how do you learn more about disconnected recordsets? For starters, take a look at this section<\/B><\/A> of the Microsoft Windows 2000 Scripting Guide<\/I>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Here\u2019s 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\u2019ve created and populated our recordset we then use the Save<\/B> method to save the data to an XML file:<\/P>

Const adPersistXML = 1\nConst adVarChar = 200\nConst MaxCharacters = 255\nConst adFldIsNullable = 32<\/p>\n
Set DataList = CreateObject(“ADOR.Recordset”)\nDataList.Fields.Append “ServiceName”, adVarChar, MaxCharacters, adFldIsNullable\nDataList.Fields.Append “State”, adVarChar, MaxCharacters, adFldIsNullable\nDataList.Open<\/p>\n
strComputer = “.”\nSet objWMIService = GetObject(“winmgmts:\\\\” & strComputer& “\\root\\cimv2”)\nSet colItems = objWMIService.ExecQuery(“SELECT * FROM Win32_Service”)\nFor Each objItem in colItems\n    DataList.AddNew\n    DataList(“ServiceName”) = objItem.Name\n    DataList(“State”) = objItem.State\n    DataList.Update\nNext<\/p>\n
DataList.Sort = “ServiceName”<\/p>\n
DataList.Save “C:\\Scripts\\Test.xml”, adPersistXML\n<\/PRE>\n
Don\u2019t worry too much about the size of the script; most of the code here involves creating and populating the disconnected recordset. We\u2019re 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:<\/P>
Const adPersistXML = 1\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. What if we don\u2019t like XML? Could we choose a different format? Well, alternatively we could define a constant named adPersistADGT and set the value to 0; this enables us to save data in the Advanced Data Tablegram format, a proprietary file format used by ActiveX Data Objects (ADO). In keeping with our philosophy, we felt XML would be the easiest format for most people to use.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
In the last line of the script we then call the Save method and save the data:<\/P>
DataList.Save “C:\\Scripts\\Test.xml”, adPersistXML\n<\/PRE>\n
As you can see, we pass Save a pair of parameters: the complete path to the new XML file, and the constant adPersistXML. It\u2019s that easy.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. OK, with one small caution: make sure that the file Test.xml does not exist before calling the Save method. Suppose Test.xml already exists and you run this script; when you call the Save method the script will fail. However, suppose Test.xml does not exist and you run the script. Inside this one script you can call the Save method (thus creating the file) and then, later on in the same script, you can call the Save method again. That\u2019s OK: because the Save method does not close the file you can continue writing to the XML file as long as the file is open. You\u2019ll encounter a \u201cfile exists\u201d error only if you close the XML file and then try overwriting it.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Of course, we know what you\u2019re thinking: what the heck am I supposed to do with an XML file? Well, here\u2019s one suggestion: use ADO to later reopen that file. Here\u2019s a very simple script that opens the file Test.xml and then echoes back the contents:<\/P>
Set DataList = CreateObject(“ADOR.Recordset”)\nDataList.Open “C:\\Scripts\\Test.xml”<\/p>\n
DataList.MoveFirst\nDo Until DataList.EOF\n    Wscript.Echo DataList.Fields.Item(“ServiceName”) _\n        & ” – ” & DataList.Fields.Item(“State”)\n    DataList.MoveNext\nLoop\n<\/PRE>\n
Easy, huh? You create an instance of the ADOR.Recordset<\/B> object and then call the Open<\/B> method, passing along the path to the XML file you want to open. <\/P>\n
With any luck this should take care of your problem, RW; if not please let us know. After all, we\u2019re well aware that sometimes things can go wrong even when you do<\/I> take the simplest possible route. For example, through a tragic mix-up our Scripting Guy\u2019s 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.)<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
  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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[19,146,3,5],"class_list":["post-67563","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-activex-data-objects-ado","tag-databases","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
  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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67563","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=67563"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67563\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=67563"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67563"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67563"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}