Apparent problem w/computed columns that use CLR UDTs.

jalbert

I've run into some trouble with SQL Compare 6.1 and computed columns that involve CLR UDTs. Here are the steps to reproduce:

1. Create an assembly.
2. Create a user-defined type ("MyUDT") that refers to the assembly.
3. Create a table that includes a persisted computed column which involves casting/converting another column within the table to the UDT created in step 2.
4. Attempt to synchronize from scripts. You'll get an error message complaining about the following column definition:

[computed_value] AS (CONVERT([MyUDT],[value],(0))) PERSISTED

What's happening is that SQL Server 2005 is expecting the "MyUDT" text to include its schema. (SQL Server requires the two-part name.) So unfortunately SQL Compare 6.1 does not appear to be able to synchronize such computed persisted columns. Is there a workaround for this issue?

JonathanWatts

Hi there,

As far as I can tell, it is not possible to create use CLR UDT in a computed column without including its schema name in the table definition. Therefore, you must add the schema name of the CLR UDT to your table definition.

Unfortunately, we cannot raise a warning if you leave out the schema as datatypes don't require this qualification.

Regards,

Jonathan

jalbert

Jonathan: I am sorry I didn't communicate that the column definition in the table DDL does indeed have the schema name of the UDT:

CREATE TABLE foo
(
	value VARCHAR(30) NOT NULL,
	computed_value AS (CAST(value AS dbo.MyUDT))
);

While this works properly when I create the table in SQL Server Management Studio, when I try to synchronize a script using SQL Compare 6.1, the "dbo." part of the UDT's name is omitted, thus causing the aforementioned error.

I'll admit this is an unusual case.

JonathanWatts

Jalbert,

Okay, I have reproduced it, it is stripping the schema in the computed column definition. It looks like a bug, which I have raised to my developer. The earliest we can get a fix for this will probably be 6.3, as 6.2 has been locked off now.

At the moment the only thing that you can do is to manually search replace the synchronisation script prior to execution.

Regards,

Jonathan