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.”
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
- 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 🙂