Changing a User Defined Type easy

[bascy]

I'm developing a VB application with a SQL-server database
All the fields in the database are of a user-defined type (UDT), so i'm sure that i.e. all my fields with telephonenumbers in it are of the same type and size.

The drawback of using user-defined types is when you want to change them. Lets say from Varchar(15) to Varchar(20).
You have to release all references from tables to the UDT, change its definition, and then re-establish all the uses of the UDT in the tables.

is there an easier way to change the UDT-definition? Does anybody have a stored procedure that can do all this for me?

That will save me a lot of work!

thanks
Bascy

 

[bascy]

mmm, :-( no responses to my question yet ...


Isn't there anybody using User Defined Types ... or do you never change them after you created them

It would save me bundles of time, please help me

thanks
Bascy

 

[Bernadette]

Hi There

We use user defined data types and I have had to amend them on one occasion and like yourself I could only drop them and re-add them using sp_addtype and sp_droptype.

However since datatypes are stored in the systypes table. You could amend it in there. I havent tried it myself on a live production server.

Example
==========
update systypes
set length = 100
where name = tMyDataType

Hope This Helps :->

Bernadette

 

[bascy]

Dear Bernadette,

thanks for your suggestion. I'v tried it but it results in the following error:

Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

Any idea how i can get arround this?

Another thing that i realised: when you change the type definition in the systypes table, does it really change the types of all the fields in my tables? Are these definitions only read when you create the column?

Hope anyone can help me further!

thanks
Bascy

 

[Bernadette]

Hi There

The error

Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

means that you are not able to modify the system tables.
To fix this

1 Right-Click on your server
2 Select Properties
3 Select the Server Settings tab
4 Check the box Allow modifications to be made directly to the system catalog
5 Click OK


I am not sure about changing the variable in systypes. I havent tested it myself but I am sure it would be no problem to you to test it, say with a User Datatype of char(5). You could change it to char(10) in systypes and see will it take more text.

Help this Helps :->
Bernadette

 

[bascy]

Thanks again for the suggestion. It appeared i was a bit lazy in posting the errormessaeg here .. i found the solution in the doc's from SQL-server

I'v tested it but it doesnt work ... you can change the UDT definition in SYSTYPES, but it doesnt change the datatype of the columns that were defined based on the UDT. Obviously, although the column remains bound to the UDT, the resulting definition, i.e. char(5), is used in the table definition :-(

Guess I'll have to keep on searching for some stored procedure someone has already created to change all the column-definitions that are bound to a UDT.

thanks again for you suggestions, Bernadette

Bascy

 

[Bernadette]

You are entirely welcome Bascy ... Sorry I cant be of further help ! Bernadette

 

[nandamurisrihari]

Bernadette,

This is srihari.
Regarding the User Defined Data Types,iam also facing the same problem like you.
Could you please help me out regarding this.
Problem:-I changed UDT from char(5) to varchar(15) in SYSTYPES table.But it doesnt change the datatype of the columns that were defined based on the UDT. Obviously, although the column remains bound to the UDT, the resulting definition, i.e. char(5), is used in the table definition .

Please suggest me regarding this
Regards
srihari