Unabletset the FieldMappings to column of type Sql_Variant

srikmrsrikmr Posts: 16
edited June 5, 2007 2:09PM in SQL Toolkit Previous Versions
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

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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 DonahueBrian Donahue Posts: 6,590 Bronze 1
    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
  • Of course. Thanks.
Sign In or Register to comment.