Changing a field in a table - won't allow

Posted on 2011-05-10
Last Modified: 2012-05-11
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?
Question by:MikeMCSD
    LVL 33

    Assisted Solution

    Try this SQL:

    alter table MyTable  alter column MyColumn nvarchar(200);
    LVL 67

    Accepted Solution

    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.
    LVL 25

    Assisted Solution

    by:Lee Savidge
    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
    LVL 16

    Author Comment

    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.
    LVL 25

    Expert Comment

    by:Lee Savidge
    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.
    LVL 67

    Expert Comment

    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

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now