Antelope Love Fan

I currently help maintain a Classic ASP (VBscript) website. The setup is pretty standard in that we have separate development and production servers. However, while I have complete programmatic access to the production database, I don’t have any sort of administrative/management console access.

This means that the dev & production data tends to drift over time and there isn’t a good way to sync them up. For a long time, I’ve been trying to come up with a way where I can slurp all of the data out of the production database and overwrite the dev database. I certainly could’ve coded something up manually but there are quite a few tables and I didn’t want to have to change my export code everytime the database scheme changed.

I was aware that it was trivial to convert an ADODB Recordset into XML and I was finally able to figure out how to load that XML back into a Recordset, then pipe it to a different database or table (provided the new destination has the same structure).

Dumping a Recordset to XML

This is dead simple

    dim objXML: Set objXML = Server.CreateObject("MSXML2.DOMDocument")
    dim res, sql

    sql = "SELECT * FROM mytable"

    set res = ExecuteReader(sql) 'This function opens a connection and returns an ADODB.Recordset

    With res
       Call .Save(objXML, 1)
       Call .Close()
    End With

    Set res = nothing

    response.contenttype = "text/xml"

    response.write objXMl.xml

First we create a MSXML2.DOMDocument object. Then We do a normal database query to get a Recordset. Last, we pass the XML object to the Save() method of the Recordset. In the example above, we go ahead and print out the raw XML (which includes a Schema that matches the definition of the database table).

The next step is to load that XML back into a recordset, then save the data to a different table and/or database. Remember, the structure of the source and destination tables must be EXACTLY the same for this to work.

We’ll do that with the following steps:

1. Open a Recordset to our destination table (without actually doing a query). The query isn’t necessary since we’re going to dynamically add all of the fields and rows to the destination Recordset.
2. Do some basic validation like “do source and destination tables have the same number of columns” and “do both tables have columns with the same names”. To be extra thorough, you could also check to verify that fields types are the same but I’m not going to do that in this example.
3. Add all of the fields from the source Recordset (the one loaded from the XML) to the destination Recordset.
4. Add each row of data to the destination Recordset. Note that we need to do an IDENTITY INSERT to make sure the ids stay consistent.

This is, obviously, going to be slow for large datasets. In that case, I think the best way to sync things up is to get a database dump/backup from production and restore it on dev.

outConn.open testConnStr

'This is an empty recordset that points to the destination database and table
outRes.activeconnection = outConn
outRes.cursortype = adOpenDynamic
outRes.locktype = adLockOptimistic
outRes.source = "mytable"
outRes.open

'This is the source XML file
res.open server.mappath("output.xml")

if res.fields.count <> outRes.fields.count then
    response.write "Skipping updates because the number of fields didn't match."
else
    for i=0 to outRes.fields.count-1
        if outRes.fields(i).name <> res.fields(i).name then
            response.write "Field mismatch:  Expecting [" & outRes.fields(i).name & "] but found [" & res.fields(i).name & "]"
        end if
    next

    while not res.eof
        outConn.execute "SET IDENTITY_INSERT dbo.mytable ON"
        outRes.addnew

        for i=0 to outRes.fields.count-1
            sfield = outRes.fields(i).name
            sval = res(sfield)
            outRes(sfield).value = sval
        next
        If outConn.Errors.Count > 0 Then
            For Each Err In outConn.Errors
                Response.Write("Error " & Err.SQLState & ": " & _
                    Err.Description & " | " & Err.NativeError & "")
            Next

            outConn.Errors.Clear
            outRes.CancelUpdate
        End If
        outRes.movefirst

        res.movenext
        numRows = numRows + 1
        outRes.update
        outConn.execute "SET IDENTITY_INSERT dbo.mytable OFF"
    wend
end if

res.close