SQL Server – resolving the issue – “Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created.”

Hi All,

LIFE IS BEAUTIFUL 🙂 I hope we all are safe 🙂 STAY SAFE, STAY HEALTHY,  STAY HOME 🙂

Background : Recently I got a chance to work bit on SQL server. So I am using SQL Server version 2019 Express edition – and installed SQL Server Management Studio – Version 18 to connect with server.

I created new table where in I need to insert some data. I realized that I have created some columns with wrong data type so I need to change there respective columns data types. So I changed the data type of few columns but when I am trying to Save I am getting an error.

Error: Following error has occurred.

“Saving changes is not permitted. The change 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. “

Solution / Workaround: Again google my friend 🙂 Bit googled and found following setting change in SQL Server Management Tool

There is setting called “Prevent saving changes that require table re-creation” in SQL Server Management Tool.

Navigate to Tools >> Options >> Designers >> Table and Database Designers >> Uncheck – Prevent saving changes that require table re-creation

This work around works like charm 🙂

But then there are few caveats.

Details: This issue occurs not only for saving changes in data types of columns but also for following points:

• 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

Basically when we use Data Definition Language (DDL) to modify a table, and then you try to save the table in Microsoft SQL Server 2008, you may receive the following message:

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.

Microsoft recommendation : Microsoft does not recommend the above work around for this problem by turning off the Prevent saving changes that require table re-creation option.

Another workaround : Use Transact-SQL statements to make the changes to the metadata structure of a table

For example : Using following SQL statements to add column to the existing table rather than doing from SQL Server Management Studio

ALTER TABLE dbo.metadataupdatedemo
ADD AddDate smalldatetime NULL

Note : Microsoft has confirmed that this is a bug in the Microsoft products – https://support.microsoft.com/en-us/help/956176/error-message-when-you-try-to-save-a-table-in-sql-server-saving-change

References:

• Error message when you try to save a table in SQL Server: “Saving changes is not permitted”
• ALTER TABLE (Transact-SQL)

Thanks for reading 🙂 Feel free to discuss / comment / questions 🙂 SHARING IS CARING 🙂

Share In Teams:

Enjoy the beautiful life 🙂 Have a FUN 🙂 HAVE A SAFE LIFE 🙂 TAKE CARE 🙂