Friday, 31 July 2015

SQL Server - Management Studio (SSMS) - Saving Changes is not Permitted

When I try to save changes to edit columns in a table and the change is very straight forward like
  • converting the type from int to long or 
  • changing the column name,
SSMS doesn't allow that. This happens even when the table has no data. Thus I am forced to drop the table and recreate it. The error I receive from SSMS (Sql Server Management Studio) while trying to save changes to data is:

"Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option prevent saving changes that require the table to be re-created."


Error Message from SQL Server Managemet Studio

This can be fixed by changing some options in SSMS.
Goto Menu
TOOLS > OPTIONS > DESIGNERS (in Options window)
Select the "Prevent saving changes that require table re-creation" option.



Now you are good to go. You can make changes to your table without the error message form SSMS.

SQL Server - Saving Changes is not Permitted

When I try to save changes to edit columns in a table and the change is very straight forward like
  • converting the type from int to long or 
  • changing the column name,
SSMS doesn't allow that. This happens even when the table has no data. Thus I am forced to drop the table and recreate it. The error I receive from SSMS (Sql Server Management Studio) while trying to save changes to data is:

"Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option prevent saving changes that require the table to be re-created."


Error Message from SQL Server Managemet Studio

This can be fixed by changing some options in SSMS.
Goto Menu
TOOLS > OPTIONS > DESIGNERS (in Options window)
Select the "Prevent saving changes that require table re-creation" option.



Now you are good to go. You can make changes to your table without the error message form SSMS.