Help on comparing sqlvariant columns

weswinkler

I'm comparing tables in two databases which have sqlvariant columns. In my "source" database, I have some records populated with varchar character data in the sqlvariant column. E.g., "Peter", "Paul", and "Mary". These have datalengths of 5, 4, and 4.

A transactional replication copies this data to my "target" database. The data appears as "Peter", "Paul", and "Mary". But, the datalengths are reported as 10, 8, and 8.

As far as I care (and any applications referencing the data on the target database), the data is identical. Apparently, the transactional replication messes with the encoding somehow, perhaps reading the source sqlvariant as a normal character but encoding it as unicode or a double-byte character.

Is there a way to convince SQL Data Compare to treat these columns as identical? :?:

Michelle T

Not currently, but we're working on an option to fix it (probably by synchronizing it as char rather than nchar data in the first place, possibly by enhancing the comparison code to deal with the situation).

(The reason that it turns into unicode is that when we synchronize any string value currently we use the N'' notation to ensure no information is lost, but while SQL Server will automatically convert that back down to single-byte characters in a varchar column, it will keep it as double-byte characters when putting it in a sqlvariant column.)