Change user type from char to vchar

We have a requirement to change a user defined type from char(4) to vchar(4).
I was wondering whether there's a easier(quickest) method to do this as opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this type.
Thanks,
Manod
ALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas

"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.c om...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod
|||Thanks Anthony...I was hoping that there would be an easier option than going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:

> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas

"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:

> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas

Change user type from char to vchar

We have a requirement to change a user defined type from char(4) to vchar(4)
.
I was wondering whether there's a easier(quickest) method to do this as oppo
sed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this ty
pe.
Thanks,
ManodALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.com...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod|||Thanks Anthony...I was hoping that there would be an easier option than goin
g
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:

> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data
,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:

> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas

Change user type from char to vchar

We have a requirement to change a user defined type from char(4) to vchar(4)
.
I was wondering whether there's a easier(quickest) method to do this as oppo
sed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this ty
pe.
Thanks,
ManodALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.com...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod|||Thanks Anthony...I was hoping that there would be an easier option than goin
g
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:

> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data
,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:

> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas

Change user type from char to vchar

We have a requirement to change a user defined type from char(4) to vchar(4).
I was wondering whether there's a easier(quickest) method to do this as opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this type.
Thanks,
ManodALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.com...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod|||Thanks Anthony...I was hoping that there would be an easier option than going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas

change publication sync_method from native to concurrent

I have a publication defined with the default sync_method of native. I have
1 subscriber subscribing to that publication . I am adding a new subscriber
and want to subscribe to it with the concurrent option. Can I change the
publication property without affecting the existing subscriber and if so how
?
right click on your publication, select the snapshot tab, and then change it
from native to concurrent. When you deploy the new subscriber it will use
the concurrent snapshot option.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:uREvlXdrFHA.2876@.TK2MSFTNGP12.phx.gbl...
> I have a publication defined with the default sync_method of native. I
have
> 1 subscriber subscribing to that publication . I am adding a new
subscriber
> and want to subscribe to it with the concurrent option. Can I change the
> publication property without affecting the existing subscriber and if so
how
> ?
>

Change ownership for User Defined Data Types

Hello,

Does anybody know an easy way to change User Defined Data Types ownership to dbo, without dropping dependent objects?

I know that there is a st. proc sp_changeobject owner, but it does not deal with this particular db objects. Is there a similar st.procedure or script that would do the same operation?

Thank youThere are no dependent object to user-defined datatypes. If you try scripting out a table that was created with user-defined datatypes, you'll find it scripts the original datatype.
Neither do objects "inherit" changes made to User-defined datatype.
Thus: User-defined datatypes are candidates for the most useless and misleading features of SQL Server, perhaps exceeded only by the Maintenance Plan Wizard.|||Well, may be I expressed myself wrong. I was trying to change ownership for UDDTs, and I the only way I knew was to drop them and recreate as "dbo". But these UDDTs were already used by many tables and stored procedures. So I would need to drop those as well and then restore the data. Too much hassle.
Anyway, I have already found the solution. This is the script:

sp_configure 'Allow Update',1
Reconfigure WITH OVERRIDE

Update sysTypes set uid = user_id('dbo') Where name = …

sp_configure 'Allow Update',0
Reconfigure WITH OVERRIDE

Change owner user defined datatype

Hello, We had a developer that created some user defined data types. He is no longer with our company and we want to change owner of those user defined data types so we will be able to delete his UUID. Is their a way to change the owner of the user defined data types from his ID to dbo. I don't see a way to change them with sp_changeobjectowner

Thanks in advance
Jef WainHere is the example, this code will change user defined datatype's owner from 'test' to 'dbo' which created by 'test' user

exec sp_configure 'allow updates', '1'

reconfigure WITH OVERRIDE

go

BEGIN TRANSACTION

update systypes set uid = user_id('dbo') where uid = user_id('test')

COMMIT TRANSACTION

exec sp_configure 'allow updates', '0'

reconfigure WITH OVERRIDE

go|||Thank you very much|||sp_changeobjectowner (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_1lpu.asp) will also do this, in a way that is supported by Microsoft.

-PatP

Change owner of user defined data types?

Hello is there a way to change the owner of a user defined data type in sql2000? If so help is appreciatedCheck this link,

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q327123

Change owner of User Defined Data Types

Please let me know if there is a method for changing the owner of existing User Defined Data Types. I cannot drop and recreate them as the datatypes are in use.
Thanks.Try, sp_changeobjectowner.
Refer to BOL for more information.|||sp_changeobjectowner cannot be used to change the owner of UDTs as it only works with tables, views, user-defined functions and stored procedures.|||Then only way is drop and recreate.|||As I stated in my original post, it is not possible to drop them as they are in use.|||Use specified SP to change owner for that table, which would be applied by default. Apart from this I have no other idea.

Change of User Defined Datatype

I want to change the user defined data type from varchar(5000) to
varchar(8000). Do I need to drop all the objects referencing the udt
and re-create them?Hi
Yes, Currently UDT's can not be changed, so a drop and re-create is
required. To drop it. it has to be removed from all the objects that depend
on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||Actually, instead of dropping the objects that reference it, you may change
the data type of the columns to the native data type and then change it
back..This is the primary reason I do not use UDTs...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#xLY9e9yEHA.1264@.TK2MSFTNGP12.phx.gbl>...
> Actually, instead of dropping the objects that reference it, you may change
> the data type of the columns to the native data type and then change it
> back..This is the primary reason I do not use UDTs...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Fred" <fredericksiu@.hotmail.com> wrote in message
> news:a5795dd.0411152215.17971860@.posting.google.com...
> > I want to change the user defined data type from varchar(5000) to
> > varchar(8000). Do I need to drop all the objects referencing the udt
> > and re-create them?
Thanks a lot.|||why not use a tool to do it instead of all that manual labour?
check out DB Ghost at www.dbghost.com
DB Ghostâ?¢ provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghostâ?¢ will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"Fred" wrote:
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
>

Change of User Defined Datatype

I want to change the user defined data type from varchar(5000) to
varchar(8000). Do I need to drop all the objects referencing the udt
and re-create them?
Hi
Yes, Currently UDT's can not be changed, so a drop and re-create is
required. To drop it. it has to be removed from all the objects that depend
on it.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.co m...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
|||Actually, instead of dropping the objects that reference it, you may change
the data type of the columns to the native data type and then change it
back..This is the primary reason I do not use UDTs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.co m...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#xLY9e9yEHA.1264@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Actually, instead of dropping the objects that reference it, you may change
> the data type of the columns to the native data type and then change it
> back..This is the primary reason I do not use UDTs...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Fred" <fredericksiu@.hotmail.com> wrote in message
> news:a5795dd.0411152215.17971860@.posting.google.co m...
Thanks a lot.
|||why not use a tool to do it instead of all that manual labour?
check out DB Ghost at www.dbghost.com
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"Fred" wrote:

> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
>

Change of User Defined Datatype

I want to change the user defined data type from varchar(5000) to
varchar(8000). Do I need to drop all the objects referencing the udt
and re-create them?Hi
Yes, Currently UDT's can not be changed, so a drop and re-create is
required. To drop it. it has to be removed from all the objects that depend
on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||Actually, instead of dropping the objects that reference it, you may change
the data type of the columns to the native data type and then change it
back..This is the primary reason I do not use UDTs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#xLY9e9yEHA.1264
@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Actually, instead of dropping the objects that reference it, you may chang
e
> the data type of the columns to the native data type and then change it
> back..This is the primary reason I do not use UDTs...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Fred" <fredericksiu@.hotmail.com> wrote in message
> news:a5795dd.0411152215.17971860@.posting.google.com...
Thanks a lot.|||why not use a tool to do it instead of all that manual labour?
check out DB Ghost at www.dbghost.com
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will bu
ild
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, fo
r
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"Fred" wrote:

> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
>