Today I faced one problem, after I installed SQL 2008. I created a database and tables, when I tried to modify some tables thru SQL Server 2008 Management studio, then I faced the following problem,
Error:
“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.”
Error window looks like,
If select Ok, I got the following window,
Solution:SQL Server 2008 Management studio is always suggests changing the design of a table by using TSQL. You can disable this by following ways,
- Open SQL Server 2008 Management Studio (SSMS) à Click Tools menu à click on Options.
- Then option windows will appear, as shown below
- In the navigation pane of the Options window, expand Designers node and select Table and Database Designers option as shown in the below snippet. Under Table Options you need to uncheck “Prevent saving changes that require the table re-creation” option and click OK to save changes.
Now if you save, it will save without any warning or error message. Hope it help you.
This is a confirmed bug, however, it is NOT recommended to uncheck the option as it may result in changes being lost.
ReplyDeletehttp://support.microsoft.com/kb/956176
Brock, I read the support article, but I'm still going to turn off this option. As a designer, I often use the Table Designer to make a change, then generate a Create Table script so I can see the correct T-SQL on how to do something, such as adding an auto-inc identity column, or a Uniqueness constraint. Since I know there will be a possible 'loss of data', I always do this on an empty table or a table I can fully re-create & populate with existing scripts.
ReplyDeleteTo me, the best answer for MS would be to 1) keep the setting default AS IS, but in the warning window give an option to "Continue". Otherwise, too much of the usefulness of Table Designer is lost.
Thanks Babu, It Helps Me
ReplyDelete