CameTooFar

A Nerd's False Positive.

Sql Server–Saving changes is not permitted, from Design View

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.

sql_server_saving_changes_is_not_permitted

Did you ever got this warning dialog box, when you try to save a Table after modifying through design-view in Sql Server 2008. You will encounter this with fresh installation of Sql Server and when tried to save an already created Table, after modification.

That’s because when you do Table modifications through the Design-View, tables are internally dropped and re-created. The reason why you get this error! However, you can by-pass this error by deselecting - Prevent saving changes that require table re-creation from Tools –> Options –> Designers. Check the below screenshot.

sql_server_prevent_saving_changes

Hope this helped.

Thanks.

Sql Server – Drop all tables and stored procedure


For last few days, I’s makings come changes to one of the website that I have hosted recently. Since, it’s dynamic website, yes, it contains dozens of tables and stored procedures. As part of testing, I find it difficult to delete the tables and stored procedure hosted in my server. Simply, because the service providers Dbase UI does not allow you to drop a database as a whole. So, every time, I have to manually delete all the tables and stored procedure which is painful as expected. Every time, I delete an object (table/stored procedure), my object explorer refreshes and takes time to reflect the changes.

In pain, I ringed one of my T-SQL buddy and he shared a script with me. Now, that simple!

Run this script, all your tables and stored procedures will be dropped at once.

-- drop all tables
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
  
-- drop all stored procedures
Declare @procName varchar(500) 
Declare cur Cursor  
 
For Select [name] From sys.objects where type = 'p' 
Open cur 
Fetch Next From cur Into @procName 
While @@fetch_status = 0 
Begin 
     Exec('drop procedure ' + @procName) 
     Fetch Next From cur Into @procName 
 End
 Close cur 
 Deallocate cur

Hope this helped.

Thanks!

Warning: ‘Saving changes is not permitted’ in Sql Server 2008

Recently, I installed the new build in of Sql Server 2008 R2. Using the Management Studio, I created and saved a Table in the Design View. Later on, I added a couple of columns to the same Table and hit the Save button. Phew…!!! I’m bombarded with this surprising warning message.

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.

A sample screenshot looks like,

sql2k8_saving_alert

I’s wondering what the changes have been made to the Sql Server 2008 Table Designer bits as compared to the Sql Server 2005 bits Table Designer. Because, you won’t get any such warning in Sql Server 2005 Table Designer.

However, You can fix this warning dialog box by disabling the option the ‘Prevent saving changes that require the table to be re-created’.

sql2k8_saving_alert_2

Here is the Exact step to enable it:

  1. Start Sql Server Management Studio
  2. Menu: Tools –> Options
  3. From the left-side Tree: choose Designers –> (uncheck) Prevent saving changes that require the table to be re-created

    Hope this helped. Thanks.