Dumping an ADODB recordset to XML, then back to a recordset, then saving to the db

Question

I've created an XML file using the .Save() method of an ADODB recordset in the following manner.

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

'This returns an ADODB recordset
set res = ExecuteReader("SELECT * from some_table) 

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

Let's assume that the XML generated above then gets saved to a file.

I'm able to read the XML back into a recordset like this:

dim res : set res = Server.CreateObject("ADODB.recordset")

res.open server.mappath("/admin/tbl_some_table.xml")

And I can loop over the records without any problem.

However what I really want to do is save all of the data in res to a table in a completely different database. We can assume that some_table already exists in this other database and has the exact same structure as the table I originally queried to make the XML.

I started by creating a new recordset and using AddNew to add all of the rows from res to the new recordset

dim outRes : set outRes = Server.CreateObject("ADODB.recordset")
dim outConn : set outConn = Server.CreateObject("ADODB.Connection")
dim testConnStr : testConnStr = "DRIVER={SQL Server};SERVER=dev-windows\sql2000;UID=myuser;PWD=mypass;DATABASE=Testing"

outConn.open testConnStr

outRes.activeconnection = outConn
outRes.cursortype = adOpenDynamic
outRes.locktype = adLockOptimistic
outRes.source = "product_accessories"
outRes.open 


while not res.eof
    outRes.addnew

    for i=0 to res.fields.count-1
        outRes(res.fields(i).name) = res(res.fields(i).name)
    next
    outRes.movefirst

    res.movenext
wend
outRes.updatebatch

But this bombs the first time I try to assign the value from res to outRes.

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Can someone tell me what I'm doing wrong or suggest a better way for me to copy the data loaded from XML to a different database?

Update, partly solved

So it turns out that my error is caused by my attempting to set the value of an Identity field. If I temporarily change that field to not be an Identity, all of the data gets inserted perfectly.

Now a followup question

How can I temporarily turn off the Identity property of that field, then turn it back on when I'm done with my updates?

score 1 · Accepted Answer · answered Nov 12 '10 at 21:42

1

I was never able to get Recordset.AddNew to work because of the above problem.

As a workaround, I'm doing a SET IDENTITY_INSERT table ON, executing the INSERT sql, and SET IDENTITY_INSERT table OFF.

answered Nov 12 '10 at 21:42

Mark Biek

146,731
54
156
201

score 0 · Answer 2 · answered May 21 '10 at 14:17

0

After reading the XML back, set the connection property of the recordset to the new database connection and then invoke UpdateBatch.

answered May 21 '10 at 14:17

Raj

1,742
1
12
17

While that doesn't give any errors, it also doesn't insert any records into the table in the new database. – Mark Biek May 21 '10 at 14:28
It has been ages since I've worked with ADO but I think you should set some property to indicate that the ADO is in "dirty" state. By the way, when you save the ADO as an XML, does it have elements? – Raj May 21 '10 at 14:47
There aren't any insert statements. It's an xml document with a Schema defining the fields and then a bunch of z:row nodes defining the actual data. – Mark Biek May 21 '10 at 14:48

Dumping an ADODB recordset to XML, then back to a recordset, then saving to the db

Update, partly solved

Now a followup question

2 Answers2