user defined types in temp tables and tables

Copper Contributor



we are Moving form sybase to MS SLQ . I  am testing a stored proc that updates a temp table . The temp table contains user defined types. 


create table #msg_address(

       temp_msg_address_id             numeric(10,0)   identity, -- 012

       msg_address_type_cd             char(20)         not null,

       msg_address                     char(64)          not null,

       msg_ref                         char(40)        null,

       party_msg_purp_type_cd      char(8)           null,

       party_id                        int        null,

       custodian_id                    int             null)



when I call the stored proc I get this error. 


Msg 2715, Level 16, State 6, Line 1
Column, parameter, or variable #2: Cannot find data type type_cd


The type does exist if I look in the user defined types .

type_cd char 20 20 NULL yes none none SQL_Latin1_General_CP1_CI_AS


if I change the table to this . it returns rows 


create table #msg_address(
temp_msg_address_id numeric(10,0) identity, -- 012
msg_address_type_cd char(20) not null,
msg_address char(64) not null,
msg_ref char(40) null,
party_msg_purp_type_cd char(8) null,
party_id int null,
custodian_id int null)


So I Am taking it  I cant use UDT in tables  ?  





1 Reply


The type does exist if I look in the user defined types .

@STEVENOCT1967 , it may exists in your user database, but temp tables gets created in the system database TempDB and there it's don't.