[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Changing a field in a table - won't allow

Using the Designer in SQL Server 2008, I want to change a field from :
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?
3 Solutions
Try this SQL:

alter table MyTable  alter column MyColumn nvarchar(200);
QlemoC++ DeveloperCommented:
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.
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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.
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.
QlemoC++ DeveloperCommented:
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.

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now