SQL Server 2008, can’t save changes to tables
Filed under: Management Studio, SQL, SQL Server 2008 — Tags: Management Studio, SQL, SQL Server 2008 — chrisbarba @ 3:49 am
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.

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.

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

Advertisements

About ambatisreedhar

Developer

Leave a Reply

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