SQL Server 2008, can’t save changes to tables

When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the management tools will not allow you to save the changes.
You will get an error stating, “You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table be re-created.”

What a pesky problem.  It prevents you from making progress when you making database changes. It’s a good thing you can turn it off.
This is caused when you make one of the following changes:

  • You change the Allow Nulls setting for a column.
  • You reorder columns in the table.
  • You change the column data type.
  • You add a new column.

Here’s how you fix it.
In Management Studio, go to Tools –> Options –> Designers –> Tables and Designers and uncheck the Prevent Saving Changes that require table re-creation option.
image

Now Management studio will work like expected.

 

WARNING:
Microsoft recommends you don’t turn this option off and that you use T-SQL to make changes to your tables.
Turning off this option will conflict if you have Change Tracking feature turned on.  If you turn off this option and make a change to table with change tracking on all the tracking changes  for that table will also be deleted.
To check if you have change tracking turned on, right click on your table and go to properties, look for the option Change Tracking.

image

Here is the kb article from microsoft: http://support.microsoft.com/default.aspx/kb/956176

About these ads

21 thoughts on “SQL Server 2008, can’t save changes to tables

  1. Thanks so much.
    I thought I was going to breeze through a simple installation. It was my first time using 2008, but I thought it would be similar to 2005.
    Then as soon as I get started I get stuck on this stupidity.
    Tried searching to no avail. until I saw your post.
    What a waste of an hour.

    Thanks for making sure I didn’t waste another hour

  2. Why do Microsoft have to constantly fk up my life.

    Thanks for the post. What were they thinking, that the millions of developers would never want to edit a schema?

    tossers

  3. sir,plz help neither i m getting change tracking nor in tools->option->designer-.>thn i m nt gettng save save change type box plz help

Got something to add?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s