Creating UDT from another UDT

Hi,
I have a User Defined Datatype, which I want to use to define another
data-type. I tried the obvious:

EXEC sp_addtype
@.typename = UDT_OBJECT_ID,
@.phystype = 'NUMERIC (5)',
@.nulltype = 'NULL'
GO

EXEC sp_addtype
@.typename = UDT_TRACKING_NUM,
@.phystype = UDT_OBJECT_ID,
@.nulltype = 'NOT NULL'
GO

but that didn't work :

Msg 15036, Level 16, State 1, Server SKINNER, Procedure sp_addtype, Line 186
The data type UDT_OBJECT_ID does not exist.

Has anyone done this before,

Thanks,
Rowland."Rowland" <banksr0@.hotmail.com> wrote in message
news:cg2b5e$njt$1@.titan.btinternet.com...
> Hi,
> I have a User Defined Datatype, which I want to use to define another
> data-type. I tried the obvious:
> EXEC sp_addtype
> @.typename = UDT_OBJECT_ID,
> @.phystype = 'NUMERIC (5)',
> @.nulltype = 'NULL'
> GO
> EXEC sp_addtype
> @.typename = UDT_TRACKING_NUM,
> @.phystype = UDT_OBJECT_ID,
> @.nulltype = 'NOT NULL'
> GO
> but that didn't work :
> Msg 15036, Level 16, State 1, Server SKINNER, Procedure sp_addtype, Line
> 186
> The data type UDT_OBJECT_ID does not exist.
> Has anyone done this before,
> Thanks,
> Rowland.

No - see the documentation for sp_addtype. You can only create a UDDT based
on a native MSSQL data type, not on another UDDT.

The best way to manage this is probably to script your UDDTs, so you can
create a new one by just changing the name in the script.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4124ed6b$1_2@.news.bluewin.ch...
> "Rowland" <banksr0@.hotmail.com> wrote in message
> news:cg2b5e$njt$1@.titan.btinternet.com...
> > Hi,
> > I have a User Defined Datatype, which I want to use to define another
> > data-type. I tried the obvious:
> > EXEC sp_addtype
> > @.typename = UDT_OBJECT_ID,
> > @.phystype = 'NUMERIC (5)',
> > @.nulltype = 'NULL'
> > GO
> > EXEC sp_addtype
> > @.typename = UDT_TRACKING_NUM,
> > @.phystype = UDT_OBJECT_ID,
> > @.nulltype = 'NOT NULL'
> > GO
> > but that didn't work :
> > Msg 15036, Level 16, State 1, Server SKINNER, Procedure sp_addtype, Line
> > 186
> > The data type UDT_OBJECT_ID does not exist.
> > Has anyone done this before,
> > Thanks,
> > Rowland.
> No - see the documentation for sp_addtype. You can only create a UDDT
based
> on a native MSSQL data type, not on another UDDT.
> The best way to manage this is probably to script your UDDTs, so you can
> create a new one by just changing the name in the script.
> Simon

Thanks for the info - I think scripting the UDDTs should work well in my
case, so I'll give that a go.

Rowland.