Accessing XML using the MSDAOSP Provider on Windows Server 2008

Jan 31, 2010  · Brandon Abshire   · 2 min read  ·
Share on:

For quite some time now, I have been using a scheduled task that executes a VBS file on my web server in order to update local weather information in a database. After porting the site to a Windows 2008 server, the scheduled task began to fail. The script is a bit out of date since the implementation of PowerShell, but there is no reason to re-write this minor script when it usually works properly.

The VBS script does the following:

1. Access current weather conditions for a zip code using the Weather.com XML data feed. 2. Save the XML to a flat file in the local directory. 3. Using the MSDAOSP Provider, connect to the XML file and open it as a record set. 4. Transform the data appropriately and insert it into a table in SQL Server which holds the current forecast. 5. Repeat for each zip code I wish to load.

The first error I encountered was "Permission Denied" even though I was an Admin user. I quickly resolved this by deleting the XML file the script was trying to overwrite, because I had previously changed the user that the script executes as.

After that error, I began to receive another error with a code of 80040E21:

OLEDB Error

Line 65 is my MSDAOSP connection string, so I assumed it was a compatibility issue with the OS. Searching the Internet for a quick fix did not return desired results, since most matches for the connection string only demonstrated how to use it.

The solution turned out to be very simple. Earlier versions Microsoft Data Access Components have been deprecated , so the Connection string was in need of a version update.

In previous versions of Microsoft Windows Server, I was able to use the following connection string: rs.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;"

In Windows Server 2008, the connection string must be updated to the following: rs.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.3.0;"

So for those of you who would like to see how to open the file using ADO, it is as follows:

myXMLfile = "c:\scripts\weather.xml" set rs = createobject("adodb.recordset") rs.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.3.0;" rs.Open myXMLfile 'Open a recordset from the newly created file

For more information on the MSDAOSP provider and how to use it to access XML in ADO, visit this Microsoft Support Article.