Working in SQL Server 2008 I received this confusing error when editing the structure of a table:
“Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. 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 to be re-created.”
I tried many things but I kept getting this error. After some trouble shooting I found the following information:
This error happens because “Saving Changes is not permitted” when using SQL Server 2008 Management Studio to modify the structure of a table especially when SQL Server needs to drop and recreate a table to save the structural changes. It is always advised to make a structural change for a table using TSQL. However, it is a convenient option for database developers to use SQL Server Management Studio make such changes as the Prevent Saving Changes That Require Table Re-creation option is enabled by default in SQL Server 2008 Management Studio.
I can understand why this feature would be helpful to have but to have it enabled by default is just silly.
I found that you can disable this feature by:
- Open the Tools menu then click on Options
- Open the “Designers” tree
- Click on “Table and Database Designers”
- Uncheck “Prevent saving changes that require table-creation”
- Press “Ok”
Once I disabled the “Prevent saving changes that require the table re-creation” option, I was ahead and save the changes I needed to make.
{ 2 comments… read them below or add one }
Great tip. I just started seeing this error myself and was dumbfounded. SQL 2005 had no such errors, so I appreciate you passing along your knowledge. Good stuff!
Glad to help. I think CitySourced is a cool app. Keep it up.