Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created.

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.”

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.

Disable “Prevent saving changes that require the table re-creation”

1.    Open SQL Server 2008 Management
Studio (SSMS). Click Tools menu and then click on Options… as shown in the
snippet below.

2.    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.

This option
when enabled prevents users from making structural changes to table using SQL
Server Management Studio especially when SQL Server needs to recreate the table
to save changes. By default, this option is checked and you need to uncheck this
option to allow users to make any structural change through SSMS that   require
table recreation.

A table needs
to be recreated whenever any of the below   changes are made to the table
structure.

·         Insert
a new column in the middle of the table.

·         Add a
new column in the table.

·         Change
the Allow Nulls setting of a column.

·         Modify
the identity property of a column.

·         Reorder
the columns within a table.

·        Modify
the datatype of a column.

Once you have
disabled “Prevent saving changes that require the table re-creation” option
you can go ahead and save the changes to the Employee Table. This will create a
Validation Warning dialog box as shown in the below snippet. The warning
message will be “One or more existing columns have ANSI_PADDING ‘off’ and
will be re-created with ANSI_PADDING ‘on’
”. Click “Yes” to save the
changes.

Risks of Turing Off “Prevent Saving Changes that Require Table Re-creation” in SSMS

If you turn off this feature then
you can avoid table recreation. However, if you have the Change Tracking
feature of SQL Server 2008 enabled to track the table changes then the change
tracking information will be lost completely whenever table is recreated. So,
it is always advised to use this feature very carefully especially in a production
environment.




Array

No comments yet... Be the first to leave a reply!