Forum Discussion

STEVENOCT1967's avatar
STEVENOCT1967
Copper Contributor
Feb 21, 2023

user defined types in temp tables and tables

hi 

 

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)

go

 

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)
go

 

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

 

 

 

 

  • olafhelper's avatar
    olafhelper
    Bronze Contributor

     


    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.

     

Resources