Sql Server Saving changes is not permitted error Prevent saving changes that require table re-creation

Question

When I create a table in SQL Server and save it  if I try to edit the table design  like change a column type from int to real  I get this error      Saving changes is not permitted  The change you have made requires the following table to be dropped and re-created  You have either made changes to a table that can t be recreated or enabled the option prevent saving changes that require the table to be re-created    Why do I have to re-create the table  I just want to change a data type from smallint to real   The table is empty  and I didn t use it until now

User · Answer

This can be changed easily in Microsoft SQL Server         Open Microsoft SQL Server Management Studio 2008    Click Tools menu   Click Options   Select Designers   Uncheck  Prevent saving changes that require table re-creation    Click OK

User · Answer

1  Open tool which is on top  2  Choose options from Picklist  3  Now Comes the popup and you can now select designers option from the list of  menus on the left side   4  Now prevent saving changes need to be unchecked that needed table re-creation       Now Click OK

User · Answer

And just in case someone here is also not paying attention  like me    For Microsoft SQL Server 2012  in the options dialogue box  there is a sneaky little check box that APPARENTLY hides all other setting  Although I got to say that I have missed that little monster all this time     After that  you may proceed with the steps  designer  uncheck prevent saving blah blah blah

User · Answer

It is very easy and simple setting problem that can be fixed in 5 seconds by following these steps  To allow you to save changes after you alter table  Please follow these steps for your sql setting    Open Microsoft SQL Server Management Studio 2008 Click Tools menu options  then click Options  Select Designers  Uncheck  prevent saving changes that require table re-creation  option Click OK Try to alter your table Your changes will performed as desired

User · Answer

Actually  You are blocked by SSMS not the SQL Server  So the solution are either change setting of SSMS or use a SQL query

User · Answer

Un-tick the Prevent saving changes that require table re-creation box from Tools   Options   Designers tab    SQL Server 2012 example

User · Answer

If you use sql server Management studio go to  Tools    Options    Designers and uncheck    Prevent Saving changes that require table re-creation    It works with me

User · Answer

From the Tools menu  click on Options  select Designers from the side menu and untick prevent changes that can lead to recreation of a table  Then save the changes

User · Answer

To change the Prevent saving changes that require the table re-creation option  follow these steps   Open SQL Server Management Studio  SSMS   On the Tools menu  click Options   In the navigation pane of the Options window  click Designers   Select or clear the Prevent saving changes that require the table re-creation check box  and then click OK   Note  If you disable this option  you are not warned when you save the table that the changes that you made have changed the metadata structure of the table  In this case  data loss may occur when you save the table

User · Answer

If you can not see the  Prevent saving changes that required table re-creation  in list like that The image  You need to enable change tracking     Right click on your database and click Properties Click change tracking and make it enable Go Tools -  Options -  Designer again and uncheck it

User · Answer

Prevent saving changes that require table re-creation      Five swift clicks                Tools   Options   Designers   Prevent saving changes that require table re-creation   OK          After saving  repeat the proceudure to re-tick the box  This safe-guards against accidental data loss    Further explanation   By default SQL Server Management Studio prevents the dropping of tables  because when a table is dropped its data contents are lost   When altering a column s datatype in the table Design view  when saving the changes the database drops the table internally and then re-creates a new one     Your specific circumstances will not pose a consequence since your table is empty  I provide this explanation entirely to improve your understanding of the procedure

User · Answer

Tools    Options    Designers and uncheck    Prevent Saving changes that require table re-creation

User · Answer

Copied from this link        Important We strongly recommend that you do not work around this problem by turning off the Prevent saving changes that require table re-creation option  For more information about the risks of turning off this option  see the  More information  section           To work around this problem  use Transact-SQL statements to make the changes to the metadata structure of a table  For additional information refer to the following topic in SQL Server Books Online  For example  to change MyDate column of type datetime in at table called MyTable to accept NULL values you can use   alter table MyTable alter column MyDate7 datetime NULL

User · Answer

From Save  Not Permitted  Dialog Box on MSDN    The Save  Not Permitted  dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created  The following actions might require a table to be re-created   Adding a new column to the middle of the table Dropping a column Changing column nullability Changing the order of the columns Changing the data type of a column      lt  lt  lt  lt   To change this option  on the Tools menu  click Options  expand Designers  and then click Table and Database Designers  Select or clear the Prevent saving changes that require the table to be re-created check box   See Also Colt Kwong Blog Entry  Saving changes is not permitted in SQL 2008 Management Studio

User · Answer

Go on Tool located at top menu  Choose options from dropdown You have a popup now select Designers option located on left hand block of menus  Uncheck the option Prevent saving changes that require table re-creation  Click on OK Button

User · Answer

Do you use SSMS   If yes  goto the menu Tools    Options    Designers and uncheck    Prevent Saving changes that require table re-creation

[sql-server-2008] Sql Server 'Saving changes is not permitted' error ? Prevent saving changes that require table re-creation

Examples related to sql-server-2008

Examples related to save

Examples related to ssms

Examples related to menuitem