Blog from Saravanan Arumugam

Let us talk about Technologies

Saving changes is not permitted


For my recent project, I was creating some tables using the SQL server designer. After creating tables, I tried to change the primary key to be of Identity column.

Exception

The change could not be saved and I received the following 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…

image

Solution

It’s a defensive mechanism that SQL server offers us to prevent accidental loss of data due to unintentional changes. It is good to have this system on.

Following list of actions might require a recreation of the table, deleting the existing records.

  • 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

However, if you are sure enough that the change is intentional and you are aware the the table would be deleted and re-created, then you may proceed with the following step to override the defense system.

Select the Tools menu –> Options in the SQL server management studio.

In the Options dialog box, expand the Designers on the tree view and select Table and Designers. On the right hand side, uncheck the option, Prevent saving changes that require table re-creation.

image

Then select Ok. Now the designer should allow you to save the changes.

Again, be aware that when you save the changes, the entire table might be deleted and the existing records might get lost to accommodate the change.

Reference: Save (Not Permitted) Dialog Box

Advertisements

One response to “Saving changes is not permitted

  1. pozycjonowanie warszawa January 18, 2012 at 12:57 am

    You speak with so much factors, so much essence, as though I become aware that you’ve clearly hit the nail on the head. Good job! I will directly grab your rss feed to stay abreast of some updates. Genuine work and much success in your business efforts!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: