Saving changes after table edit in SQL Server Management Studio

Question

If I want to save any changes in a table  previously saved in SQL Server Management Studio  no data in table present  I get an error message      Saving changes is not permitted  The changes 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    What can prevent the table to be easily edited  Or  is it the usual way for SQL Server Management Studio to require re-creating table for editing  What is it - this  option Prevent saving changes

User · Answer

Rather than unchecking the box  a poor solution   you should STOP editing data that way  If data must be changed  then do it with a script  so that you can easily port it to production and so that it is under source control  This also makes it easier to refresh testing changes after production has been pushed down to dev  to enable developers to be working against fresher data

User · Answer

Go into Tools -  Options -  Designers-  Uncheck  Prevent saving changes that require table re-creation   Voila   That happens because sometimes it is necessary to drop and recreate a table in order to change something  This can take a while  since all data must be copied to a temp table and then re-inserted in the new table  Since SQL Server by default doesn t trust you  you need to say  OK  I know what I m doing  now let me do my work

User · Answer

GO to SSMS and try this  Menu    Tools    Options    Designers    Uncheck    Prevent Saving changes that require table re-creation      Here is a very good explanation on this  http   blog sqlauthority com 2009 05 18 sql-server-fix-management-studio-error-saving-changes-in-not-permitted-the-changes-you-have-made-require-the-following-tables-to-be-dropped-and-re-created-you-have-either-made-changes-to-a-tab

User · Answer

To work around this problem  use SQL statements to make the changes to the metadata structure of a table   This problem occurs when  Prevent saving changes that require table re-creation  option is enabled   Source  Error message when you try to save a table in SQL Server 2008   Saving changes is not permitted

User · Answer

Tools Options    Uncheck above option

User · Answer

This is a risk to turning off this option   You can lose changes if you have change tracking turned on  your tables    Chris  http   chrisbarba wordpress com 2009 04 15 sql-server-2008-cant-save-changes-to-tables

User · Answer

Many changes you can make very easily and visually in the table editor in SQL Server Management Studio actually require SSMS to drop the table in the background and re-create it from scratch  Even simple things like reordering the columns cannot be expressed in standard SQL DDL statement - all SSMS can do is drop and recreate the table   This operation can be a  very time consuming on a large table  or b  might even fail for various reasons  like FK constraints and stuff   Therefore  SSMS in SQL Server 2008 introduced that new option the other answers have already identified   It might seem counter-intuitive at first to prevent such changes - and it s certainly a nuisance on a dev server  But on a production server  this option and its default value of preventing such changes becomes a potential life-saver

[sql] Saving changes after table edit in SQL Server Management Studio

Examples related to sql

Examples related to sql-server

Examples related to ssms

Examples related to database-table