Column, parameter, or variable #1: Cannot find data type XYZ

The following Error occurred when we refer a user defined datatype which was created in another database. Even in different database or creating temporary tables ( #tables (or) ##tables ). Temporary tables created in Tempdb database.

/*Creating User defined datstype in DB1 database*/
Use DB1
Go
Create Type UDT_EMail From NVarchar(100)
Go

/*Refering the type when creating Temporary table*/
Create Table #Table1
(
Id Int Identity(1,1),
EMail UDT_EMail
)
 
The following error occurred
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #2: Cannot find data type UDT_EMail.
 
#Table1 will be created in Tempdb database, So, It could not find any data type in Tempdb database. But, It can be refered by Table variable..

/*Refering the user defined type in Table varible*/ 
Use DB1
Go
Declare @Tbl_Variable Table
(
EMail UDT_EMail
)

Is there any way to refer the Type in another database using three part naming convension ?

/*Refering the type using three part naming convension*/
Use DB1
Go
Create Table #Table11
(
Id Int Identity(1,1),
EMail DB1.dbo.UDT_EMail
)

The following error occurred
 
Msg 117, Level 15, State 2, Line 4
The type name 'DB1.dbo.UDT_EMail' contains more than the maximum number of prefixes. The maximum is 1.

Because, The type allows only 1 prefix, not morethan 1.

Note: No way to refer the User defined type in Tempdb (or) another database unless the Type created in that particular database itself. Even the type was created in Master database.

No comments:

Post a Comment