Re: How to change column data type from Text to nvarchar()

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: EradicusMax (dwinter_at_attbi.com)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 14:22:52 -0400

When in the design table interface, it is the 3rd icon from the left at the
top. Initially disabled, if you change something in the table it will become
enabled. It is next to the properties menu icon.

"mitra" <mitra@discussions.microsoft.com> wrote in message
news:97C5EFCC-6CFA-44F9-885D-79972497FD91@microsoft.com...
> Yes, I need to script the column data type changes to send it to all of
our
> customers. There are a lot of changes and we don't want them to do it
> manually using the EM.
>
> You said if I want to find out how EM does it I could click the "save
> script" icon.
>
> Where is the save script icon in SQL Server Enterprise Manager 2000?
>
> When I change the data type using EM, I am prompted with the message that
do
> I want to save the changes and after I click the yes button, I get a
"Save"
> message window that says "The following tables will be saved to your
> database. Do you want to continue?" There is a "Save Text File" button on
> this "Save" Dialog box that saves the list of the table affected by the
> changes I made. That's all!
>
> Please tell me where is the save script icon that saves the actual script.
I
> like to see the code that the EM used to change a data type from Text to
> nvarchar.
>
> Thank you,
>
> --Mitra
>
> "EradicusMax" wrote:
>
> > Do you need your own custom script for some purpose or not have access
to
> > Enterprise Manager? In EM right click on table, choose design, go to
your
> > text field and change it to the nvarchar(4000), click save. If you want
to
> > know what EM is doing, click the save script icon and it will generate
the
> > script it will use to change the field.
> >
> > "mitra" <mitra@discussions.microsoft.com> wrote in message
> > news:BAC648AA-CB1F-4C8E-B2FD-26849B47AD3F@microsoft.com...
> > > Hello,
> > >
> > > I need help with changing a column data type from Text to a
> > nvarchar(4000).
> > >
> > > I tried the syntax below in QA and I got an error:
> > >
> > > ALTER TABLE table_name
> > > ALTER COLUMN column_name nvarchar(4000)
> > > GO
> > >
> > > Server: Msg 4928, Level 16, State 1, Line 1
> > > Cannot alter column 'message' because it is 'text'
> > >
> > > I don't need to worry about losing any data. What's the best approach
to
> > > change TEXT data type to NVARCHAR()?
> > >
> > > I also noticed that I need to drop indexes and the constraints before
> > being
> > > able to change the data type.
> > >
> > > I have several DEFAULT CONSTRAINTS on this one table that I don't know
the
> > > CONSTRAINT names.
> > >
> > > I used "sp_helpconstraint table_name" to get the CONSTRAINT names. It
> > > returns all the Constraint names on a table.
> > > I think I just need to drop the constraint on the column that I want
to
> > > change its data type.
> > > I can drop the constraint if I type in the full Constraint Name but
not if
> > I
> > > use "LIKE" syntax and the "%" character. I got an incorrect syntax
error:
> > >
> > > ALTER TABLE table_name
> > > DROP CONSTRAINT LIKE 'DF__table_name__fvod%'
> > >
> > > Server: Msg 156, Level 15, State 1, Line 2
> > > Incorrect syntax near the keyword 'LIKE'
> > >
> > > Can wildcard character be used to drop a constraint. If yes, what's
wrong
> > > with the above syntax?
> > >
> > > --
> > > Mitra
> >
> >
> >

Quantcast