Error message when you try to save a table in SQL Server 2008: "Saving changes is not permitted"

on
This problem occurs when the Prevent saving changes that require the table re-creation option is enabled, and you make one or more of the following changes to the table:
  • You change the Allow Nulls setting for a column.
  • You reorder columns in the table.
  • You change the column data type.
  • You add a new column.
When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the error message

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















Risk of turning off the "Prevent saving changes that require table re-creation" option

Although turning off this option can help you avoid re-creating a table, it can also lead to changes being lost. For example, suppose that you enable the Change Tracking feature in SQL Server 2008 to track changes to the table. When you perform an operation that causes the table to be re-created, you receive the error message that is mentioned in the "Symptoms" section. However, if you turn off this option, the existing change tracking information is deleted when the table is re-created. Therefore, we recommend that you do not work around this problem by turning off the option.

To determine whether the Change Tracking feature is enabled for a table, follow these steps:
  1. In SQL Server Management Studio, locate the table in Object Explorer.
  2. Right-click the table, and then click Properties.
  3. In the Table Properties dialog box, click Change Tracking.
If the value of the Change Tracking item is True, this option is enabled for the table. If the value is False, this option is disabled.

When the Change Tracking feature is enabled, use Transact-SQL statements to change the metadata structure of the table.

APPLIES TO
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup

Reference :Microsoft

Comments

Post a Comment