Unabletset the FieldMappings to column of type Sql_Variant

srikmr

Hi,
I'm tring to comapre two tables in different databases. The custom comparision fields is of the datatype sql_variant.

when i map it, it throws me an error.
Below is my code.

// Create the mappings
                tableMapping = (TableMapping)mappings.Join(db1.Tables["[dbo].[Table1]"], db2.Tables["[dbo].[Table1]"]);
                // Set the custom comparison key for the table
                tableMapping.MatchingMappings.Clear();
                tableMapping.MatchingMappings.Add(tableMapping.FieldMappings["CustomID"]);

I wont get the error if i give any other column which is other than sql_variant.
Let me know how to resolve this issue.
Regards

Brian Donahue

Hi,

I believe this is expected behaviour. SQL Data Compare doesn't allow a datatype of sql_variant as a comparison key column. This is from the help file:

Note that a comparison key cannot include columns whose data type is image, ntext, nvarchar(max), sql_variant, text, varbinary(max), varchar(max), or xml.

This may be another circumstance where creating a view on the table in each database and using the CAST or CONVERT function to transform the sql_variant into another datatype, then telling SQL Data Compare engine to compare the view in place of the table, may be a good workaround for this.

alexstyler

Brian,
I was writing my own automatic mapping code (finds primary keys, matches tables) that allows me a little more specialization than SchemaMappings. I did not test any columns of these types during my unit tests (looks like I need more coverage). I was wondering the behavior of this error.

For example, if I'm looping like:

foreach(string key in primaryKeys)
  tableMapping.MatchingMappings.Add(tableMapping.FieldMappings[key]); 

and it comes across a column of an illegal type, what exception is thrown, and what happens to the table mapping? Let's assume I wrap this with a try, catch. I can assume the illegal columns are not included in the mapping, but does the whole mapping get destroyed or is the rest still intact?

Thanks,
Alex

Brian Donahue

I don't think it throws an error, but when you refresh the mappings, each mapping's Status property is set to to one of

• TableMappingStatus.UnableToCompare
• TableMappingStatus.Success
• TableMappingStatus.PartialSuccess

alexstyler

Of course. Thanks.