SQL Server: 'Saving changes is not permitted.'

Tags: , | Posted by Kevin Babcock on 7/5/2009 11:38 PM | Comments (4)

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.

I ran into this fun little error message again tonight. It’s an annoying warning that pops up in SQL Server 2008 when performing certain changes to an existing schema. Apparently SQL Server 2008 drops and re-creates your tables when you make certain changes, so it keeps you from accidentally screwing up your database by providing this error. There is a setting which you can change to get around this “feature” but I highly recommend you think it through before just turning the warning off. After all, you wouldn’t want to inadvertently drop an important table would you?

To turn the warning off, go to Tools | Options | Designers | Table and Database Designers in SQL Server Management Studio and de-select the "Prevent saving changes that require table re-creation" checkbox.

SQL Server Management Studio Options

You can also turn the setting off from Visual Studio by de-selecting the same checkbox in Tools |Options | Database Tools | Table and Database Designers | Table and Database Options.

Enjoy!

Comments

Matt Sherman on 7/6/2009 1:20 AM I've run into this as well. I am unclear whether the "drop" is destructive -- does it actually wipe out data? Or is the warning more about performance, ie, do you want to lose availability while this happens?

I can't imagine that updating a schema should wipe out a whole table.
Jonathan Simmons on 7/7/2009 7:39 PM Where SQLServer 2008 filestream is used, making changes to a table which involve the table being dropped and recreated causes disastrous results. Even when a field is varbinary(max) with the filestream attribute added, if a change is made to the structure causing this dropping and recreating, the table is indeed recreated but the filestream attribute is quietly 'forgotten' by SQLServer 2008. This means that if you already have filestream data stored, you loose the association between the database and the filestream data which in effect means all that data have gone. If you are using filestream, always check the option 'Prevent saving changes that require table re-creation' in SSMS, just in case you have a moment of madness and forget about the filestream data!
Kevin Babcock on 7/7/2009 8:40 PM @Matt: In doing some quick testing, I see that my data is persisting across schema changes even though the table is being recreated. However, I'm no SQL Server expert so I'm sure there are instances (as Jonathan points out) where this will not be the case. I'm assuming the default setting is there for a reason.

@Jonathan: Thanks for the great explanation!
Aaron on 7/20/2009 12:24 PM Anyone doing anything more than even the lightest maintenance needs to have this turned off.

Somehow I have survived since SQL server 2000 without this useless warning.

Add comment




biuquote
  • Comment
  • Preview
Loading