We will try to get rid of below Error in this Article. “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 to option Prevent saving changes that require the table to be re-created.”
This article will help the Beginner and Basic Users of Microsoft SQL and tacle problem which prevent alter SQL table which contains data. As we all know, all kind of Application developed with .NET. Whether it is Windows based or Web based use databases and in most cases Microsoft SQL.
The reason behind this might be simply easier to learn and simple Queries. Also as being Microsoft Product, it is also included with .NET framework with the name of ADO.NET.
Microsoft SQL is a very vast and complicated RDBMS application. To make this easy Microsoft provides an applications with simple graphical user interface named Microsoft SQL Server Management Studio (in short SSMS).
SSMS helps performing all basic tasks like Creating Database, Adding Tables, Running Queries, Viewing Results, Adding Data, Altering Data, etc and other various management tasks. And Best of its feature is, it saves a lot of Time. You need not to write Complicated Query each time.
But for security reasons, SSMS creates some problems with performing some critical operations using default setting.
Today we will change setting of SSMS to solve one of most common problem.
The Problem (Can’t Alter SQL Table with data)
You must have faced a problem. I wont describe it by words, instead I will show you what it use to Look like.
Now, remember, how many times does this dialogue annoyed you and created problem. The above snapshot is captures which I tried to Add a Row in existing table that has Relations and some data.
Basically Error is simple and Logical. It is telling that, you have Table, that has Data and if you want to change the Structure of Table, you have to DROP Table and CREATE new one. However, we don’t want to Loose our data & create complications.
Solution
Now without wasting our time, lets move to Process.
1. Click on Tools Menu on SSMS and choose Options. Following Windows will open.
2. Expand Designers Node and choose Table and Database Designers.
3. Now un-check the Options, Prevent Saving Changes that require table re-creation. And Click OK.
Now, you are back to SSMS and try to Save your changes by Just Pressing Ctrl+S. You will see following screen.
Click Yes button to save changes you made. Congratulations, the annoying Screen as in First screenshot telling that You Can’t … will never appear again.
Hope this article about “Alter SQL Table with data” will be helpful to you. You can read all other articles on SQL & ASP.NET.
I strongly recommend to undo this settings for security reasons. Suppose a case where you unknowingly deleted some column by mistake and hit save button, loss may be irreversible. Please use with caution.
This article is first written by me on DotNetSpider.com.
