Forum Discussion
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 ?
- olafhelperBronze 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.