Tuesday, January 6, 2009

SQL Server 2008: "Saving changes is not permitted"

When trying to save a table in SQL Server 2008 error comes up "Saving changes is not permitted" The statement is like this
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.
While searching on the net i found out that there are some more chnages which we do on the table in SQL server 2008 and that will cause same error above.

Below are some of the
  1. change the Allow Nulls
  2. setting for a column.
  3. reorder columns in the table.
  4. change the column data type.
  5. add a new column.

This Warning message pops up because we create a New schema for the Table. This schema change might loose already existing data in the table it will also change the meta data of the table.
To get rid of this waring message

  1. Open SQL Server Management Studio (SSMS).
  2. On the Tools menu, click Options. In the navigation pane of the Options window,
  3. click Designers. Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

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.

No comments: