Faketable fails when table uses a uddt as a colum type

[rkr]

Hi,

There seems to be a problem when trying to fake a table that contains columns whose type is a user defined data type.
The test fails with the message: "COLLATE clause cannot be used on user-defined data types".

Here is some code to reproduce:

EXEC tSQLt.NewTestClass 'fake_tables_with_uddt';
GO
CREATE TYPE fake_tables_with_uddt.some_type FROM NVARCHAR(20)
GO
CREATE TABLE fake_tables_with_uddt.some_table (some_column fake_tables_with_uddt.some_type)
go
CREATE PROCEDURE fake_tables_with_uddt.test_can_fake_table_with_uddt_column
AS
BEGIN
    EXEC tSQLt.FakeTable 'fake_tables_with_uddt.some_table';
END
GO
EXEC tSQLt.RunTestClass 'fake_tables_with_uddt';
DROP TABLE fake_tables_with_uddt.some_table;
DROP TYPE fake_tables_with_uddt.some_type;
EXEC tSQLt.DropClass 'fake_tables_with_uddt';

As a little extra: DropClass does not drop uddts. Thats why there are the extra drop statements for the table and the uddt just before the DropClass.

I assume that this only applys to uddts that with an underlying type of varchar, nvarchar and whatever else might have a collation.

Any plans on changing this behavior?

Regards
r

[tS...@sqlity.net]

Hi Ralf,

I just ran into this problem myself last week. It is on the backlog to be fixed, but there is no ETA yet.

Thanks

Sebastian

[Chris Francisco]

FYI I ran into the same issue and was able to get around it by digging into the tSQLt code a bit.  There's a Function named Private_GetFullTypeName

The last column returned by the function is named "Collation"  which returns an empty string '' if the variable @CollationName is null.  If you add "OR is_user_defined = 1" to the condition for the empty string I've been able to fake tables with user defined data types.  

I haven't run into any weird side affects from this alteration but I'm no expert on the inner workings of tSQLt (or on tSQLt in general) so this may break functionality elsewhere.  

[Kenny Evitt]

Hi Sebastian,

I just ran into this problem myself. I'm going to try some of the workarounds that others, like Chris Francisco in this thread, have mentioned. What's the process required to contribute changes?

Just to add to fragments of discussion I noticed with regard to the hosting of the source code, GitHub would be very convenient for me personally to contribute.

Thanks,

Kenny

[Kenny Evitt]

I made the change that Chris described and it resolved the user-defined-type-collation error for me too.

I'll reply again if I run into any issues that seem to be caused by that change.

[Sebastian Meine]

Sorry for this fix to take so long, but in the newest version this should be fixed. Let us know if there are any more problems.

Sebastian