Changing a field in a table - won't allow

Using the Designer in SQL Server 2008, I want to change a field from :
nvarchar(400)
to
nvarchar(200).
I get the message :
"Saving changes is not permitted. The changes you have made require the following tables to be  droppped and re-created . .  ".

How can I get around this?
LVL 16
MikeMCSDAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Your Management Studio is set to the default, which prevents you from accidentally make severe changes affecting other tables. Go into your Options, Designers, Table and Database Designer, and uncheck the last option in the upper area (should something along "Prevent from saving ... recreate ...".

If you do not have anything depending on the column you want to change, you can indeed use above SQL. However, that does not work if there are constraints, indexes, keys, or statistics defined for that column.
0
 
knightEknightCommented:
Try this SQL:

alter table MyTable  alter column MyColumn nvarchar(200);
0
 
Lee SavidgeCommented:
This is a SQL 2008 thing and IMHO it's rather dumb...

Tools -> Options -> Designers -> Table and Database Designers and untick the box that says:

Prevent saving changes that require table re-creation

Press OK
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MikeMCSDAuthor Commented:
thanks guys . .  that fixed it.

Is it safer to use the Designer instead of SQL when making changes like this?


>> This is a SQL 2008 thing and IMHO it's rather dumb...

You want to see dumb, start using Windows 7. It blocks you from doing a lot of things and I hate it.
0
 
Lee SavidgeCommented:
It is up to you what you're more comfortable using to be honest.

As for Win 7... that's a different (and long) story.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
As I have said in http:#a35730110, if there are dependencies like index aso. you need to use SSMS. It will do all necessary steps to perform the change. Doing that yourself in SQL is a PITA.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.