BatchMove exception: column specified more than once

[corneliusd]

I'm using the CRBatchMove components to transfer all data from a local embedded database to MS SQL Server and it's working well for most of my tables. However, on two of them, I'm getting a strange error and I can't see anything particular about the data or tables in these instances.

Here's the error message that appears: "The column name XXX is specified more than once in the SET clause or column list of an INSERT."

In one case, it's a small table and the column is a VARCHAR(200) with most values NULLs and the rest containing a URL (the error happens the first time a non-NULL value appears). In the other case, a DateTime field raises three instances of this error all at once.

Since I'm converting all data over, the tables are empty before I start and the data structures are simple with no triggers or foreign key relationships. I've checked the source datasets there is only one instance of the named fields in each case. I've specified mmFieldName as the FieldMappingMode and listed all the fields in the Mappings list of the BatchMove component. The destination is a TUniTable using the SQLServer Provider.

This project is in Delphi XE and is using UniDAC 7.2.7.

Any thoughts on how I can get past this?

[Stellar]

Unfortunately, we cannot reproduce the issue with duplicating fields. Please compose a small sample to reproduce it and send it to us for investigation. You can send the sample using the contact form at our website:
http://www.devart.com/company/contactform.html .

[corneliusd]

I tried creating a simple example as well and failed. So I went back to my project and fixed the problems after lots of tweaking and testing. And I fixed them in different ways.

In both cases, the fields that were causing the error were the last field in the table. When I moved the table to a different position in the destination table definition, a different field caused the error, but always the last field--whichever field was the last field.

A few more similar tests like this confirmed to me that it is not something in my code or options I was setting, but something in the BatchMove or TUniTable components that if something is just off a bit, it can throw this error.

I still don't know what caused it, but I'll share how I got around both problems...

In one of the tables, I am importing GUIDs as strings from the source table and using SQL Server's automatic conversion to set uniqueidentifier in the destination SQL Server table. What I had to do with these fields was open the DataTypeMap property of the TUniTable, list the fields, and specify them as a String field type. To get around the "column specified more than once" error on these fields, I simply checked the "Ignore Error" box on those fields in their DataTypeMap.

In another table, I found that simply setting a primary key on the SQL Server table and selecting that field name in the IndexFieldNames property of TUniTable component seemed to make the error go away.

Neither of these really make sense for the error message I received, but it gives me hope that if I see this again with a different table, I can just play around with different options until I find the right combination to make this strange error go away. I certainly don't have any fields actually listed twice--partially because I'm not setting the fields, but simply using the TUniTable which discovers the fields for me. And the CRBatchMove component is building the clause for me, so it's not really anything I can directly control.

Perhaps this helps someone else who may stumble across this--or that the problem can be found and resolved in the component set someday.

Cheers!

David Cornelius

[Stellar]

Glad to see that the issue was resolved. It is a pity that you could not compose a sample that would demonstrate the issue.
Once you find the issue cause, please send us a sample that demonstrates it.