User defined datatype - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

User defined datatype

Author

Topic

sbalaji
Starting Member

48 Posts

Posted - 2007-08-31 : 07:37:01

We r using userdefined datatype udd_mobileno = varchar(10)
now v want to alter it to varchar(15)

This datatype is used in more than 200 objects tables and sps,

i have tried this solution

exec sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE

update systypes
set length = 15
where name = 'udd_mobileno'

but the change in length is not reflecting in the table datatypes

so i am trying to find all the objects with this datatype and recompiling it,is there any other way???

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-31 : 20:35:40

I saw someone ask this on another site a while back for the same issue, this was the solution (comments not in English.):

--------------------------------------------
Make New data type example: EmpID_10
and i think it will work for your problem:

CREATE PROCEDURE dbo.P_CHANGE_DATA_TYPE AS
/* Change data type is your new data type alik 07/05/2003*/
DECLARE
[Email address protected] VARCHAR(50), -- Current Data Type (it will
change)
[Email address protected] VARCHAR(50), -- New Data type
[Email address protected] VARCHAR(50),
[Email address protected] VARCHAR(50)

SET [Email address protected] = 'EmpID' -- bu veri tipli kolonlar
SET [Email address protected] = 'EmpID_10' -- bu veri tipine
dÃ¶nÃ¼Ã¾tÃ¼rÃ¼lecek

BEGIN TRANSACTION

DECLARE Tables CURSOR FOR
SELECT o.name TableName, c.name ColumnName/*, convert
(sysname,case

when t.xusertype > 255 then t.name
else
d.TYPE_NAME collate database_default
end)
TYPE_NAME*/ --type name buradan geliyor kontrol iÃ§in! alik 08/05/2003
FROM
sysobjects o,
/* master.dbo.spt_datatype_info d,*/
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE c.id = o.id
/* AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)*/
AND o.type = 'U' --Tablolar
/* AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name,
'IsIdentity'),0)*/
AND c.xusertype = t.xusertype
AND t.name = [Email address protected]
ORDER BY o.name
OPEN Tables
FETCH NEXT FROM Tables INTO [Email address protected] [Email address
protected]

WHILE [Email address protected] = 0
BEGIN

EXEC('ALTER TABLE ' + [Email address protected] + ' ALTER COLUMN ' +
[Email address protected] + ' '
+ [Email address protected]

FETCH NEXT FROM Tables INTO [Email address protected] [Email address
protected]
END

IF [Email address protected] != 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

CLOSE Tables
DEALLOCATE Tables

GO

------------------------
Future guru in the making.

sbalaji
Starting Member

48 Posts

Posted - 2007-09-03 : 05:52:43

Thanks for the post,i have come with this,which updates all objects (sp,tables,views)

Create procedure Change_udd_sp
@udd_name varchar(48),--Udd name already existing
@udd_new_length int--new length
as
begin
exec sp_configure 'allow updates',1

RECONFIGURE WITH OVERRIDE

/*Update datatype length*/
update systypes
set length = @udd_new_length
where name = @udd_name

/*Update columns*/
UPDATE syscolumns
SET LENGTH = @udd_new_length
WHERE type_name(xusertype) = @udd_name
end

BonnieB
Starting Member

2 Posts

Posted - 2007-10-18 : 19:10:00

Oh, sbalaji, this is exactly what I have been looking for!! Thank you!

However, I have some issues with it. I can't create a Stored Procedure, as you have. I'm basically using the same code as you, but it seems the T-SQL in the Stored Proc is being executed, instead of the Stored Proc being created.

I've never encountered this sort of thing before when creating a SP. I'm getting the following error message when trying to create the SP:

Msg 259, Level 16, State 1, Procedure Change_udd_sp, Line 11
Ad hoc updates to system catalogs are not allowed.

Any ideas as to what the problem might be?

quote:
Originally posted by sbalaji

Thanks for the post,i have come with this,which updates all objects (sp,tables,views)

Create procedure Change_udd_sp
@udd_name varchar(48),--Udd name already existing
@udd_new_length int--new length
as
begin
exec sp_configure 'allow updates',1

RECONFIGURE WITH OVERRIDE

/*Update datatype length*/
update systypes
set length = @udd_new_length
where name = @udd_name

/*Update columns*/
UPDATE syscolumns
SET LENGTH = @udd_new_length
WHERE type_name(xusertype) = @udd_name
end

~~Bonnie
[C# MVP]

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-10-19 : 16:22:20

quote:
Originally posted by BonnieB

Oh, sbalaji, this is exactly what I have been looking for!! Thank you!

However, I have some issues with it. I can't create a Stored Procedure, as you have. I'm basically using the same code as you, but it seems the T-SQL in the Stored Proc is being executed, instead of the Stored Proc being created.

I've never encountered this sort of thing before when creating a SP. I'm getting the following error message when trying to create the SP:

Msg 259, Level 16, State 1, Procedure Change_udd_sp, Line 11
Ad hoc updates to system catalogs are not allowed.

Any ideas as to what the problem might be?

~~Bonnie
[C# MVP]

Possibly exactly what the error message you quoted says:

Ad hoc updates to system catalogs are not allowed.

The error message also specifies a line in the stored procedure (11), which equates to the "update systypes" line.

This means that you're either running a different version of SQL Server than sbalaji, or you don't have sufficient rights to alter system-maintained tables (probably the first).

mattyblah
Starting Member

49 Posts

Posted - 2007-10-19 : 18:20:38

IIRC, the problem with UDDT is that they take the form of the base datatype, in other words, sysname datatypes take the form nvarchar(128). if sysname is changed, well the predefined sysname types changes. i thought this was a know limitation on uddt (user defined datatypes).

BonnieB
Starting Member

2 Posts

Posted - 2007-10-19 : 19:22:32

quote:
Originally posted by KenW

Possibly exactly what the error message you quoted says:

Ad hoc updates to system catalogs are not allowed.

The error message also specifies a line in the stored procedure (11), which equates to the "update systypes" line.

This means that you're either running a different version of SQL Server than sbalaji, or you don't have sufficient rights to alter system-maintained tables (probably the first).

Yeah, Ken ... I'm using SQL 2005. I have a feeling that sbalaji is using SQL 2000.

I've read a few other articles/posts since I posted my question and the bottom-line is that this is not supported anymore in SQL 2005. It apparently was not supposed to be used anyway, not even in SQL 2000, even though it worked.

So, I'm looking for other ways to do this. Any suggestions?

~~Bonnie
[C# MVP]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources