Problem with Comparison of CLR type

Wyatt70

SQL Server Version: 9.0.3054
SQL Data Compare Version: 6.0.0.1124

I am attempting to compare two databases, each of which has several tables with a column whose data type is a CLR data type. The comparison engine is able to process some of the tables using this type, but is not able to process other tables using this type.

Here is the error message: The type "UserInformation" is not comparable. It cannot be used in the ORDER BY clause.

The problem gets a little more interesting when you consider the history of these tables. In an earlier attempt at synchronization, I dropped and recreated the columns using the CLR type for some of the tables. All of the columns for which I dropped and recreated the column were able to be compared. However, I was also able to compare one of the tables for which I made no modifications.

The CLR type is used to store information on the user who originally populated the column, and the user who made the most recent data update to the column.

Here is an example of the data stored in the column:

Created On: 1/22/2008 12:00:00 AM,Created By:  KEPOWERS,Modified On: 1/22/2008 12:00:00 AM,Modified By:  KEPOWERS

Nothing fancy, just a datetime value and a character string.

Any ideas on why SQL Data compare is choking on this?

richardjm

What comparison keys are you using on the different tables, i.e. the one that works vs the one that doesn't?

Perhaps you could try setting the 'transport clr types as binary' option in the project settings to see if that makes a difference.

The other thing is to run a SQL Profiler trace when you're comparing the tables to see what command SQL Data Compare uses when reading the table to see if that works in SQL Management Studio or something. It should be something like...

 select col1, col2, col3 from mytable order by col1

Wyatt70

The tables in question are in two different schemas. All but one of the tables that work are in one schema, and all of the tables in this schema have primary keys. The tables in the other schema do not have primary keys, so I have to create a custom comparison key for each table that includes all columns in the table.

richardjm

What did the select statement look like from SQL Profiler - did it run in SQL Management Studio as a select statement on it's own.

I wonder if selecting the CLR type as part of the comparison key is what is causing the issue - in the tables that work is the CLR type part of the comparison key or not?

Wyatt70

I caputure the SQL statements in Profiler, and I was able to run them all in Management Studio. As for the comparison key, it is not included for the tables that have a primary key, but it IS included for the tables that do NOT have primary keys.

richardjm

OK I think that explains it then - the error you're getting

The type "UserInformation" is not comparable. It cannot be used in the ORDER BY clause. 

Comes straight from SQL Server as is saying that your CLR data type doesn't implement IComparable so can't be used in an order by statement. I think if you wrote.

select UserInformation from MyTable order by UserInformation

SQL Server would complain in exactly the same way.

If you exclude the CLR data type from the custom comparison key it should work as long as you don't rely on that field being part of your 'uniqueness' constraint. Otherwise you'll have to implement IComparable for your type.

HTH

Wyatt70

I'll speak to the developer, she may be able to modify it to implement IComparable. Thanks for the assistance!