Wednesday 24 October 2012

Could not find data type X.Y when creating a temporary table

Just a reminder: I was trying to create a temporary table in SQLServer 2k8 using a user-defined data type, a bit like:

use MyDB;

create table #myTempTable
(
    MyField udt.MyType_t not null
)

but hit

Could not find data type udt.MyType_t

This is because the type is only defined in my database MyDB – it’s not defined in TEMPDB. When you try to use it when creating a temporary table the database can’t find it.

The workaround is to use the underlying data type instead.

No comments: