This blog explains the following,
- How to create the table
- How to alter the table (How add/modify Column/DataType in existing table)
- Ways to prevent Saving changes is not permitted in SQL Server
Step 1: Create table
use the following sql script to create Mas_Employee table.
- Create table Mas_Employee(
- ID int primary key,
- Name varchar(50),
- Salary int,
- DeptID varchar(10)
- )
For example, If you want to change the DataType of DeptId from Varchar(10) to int do the following steps:
- Goto "Object Explorer" window.
- Your Database.
- Tables.
- Right click on "Mas_Employee".
- And select "Design".
- Change the datatype from varchar(50) to int.
- Save the table.
At this point, you will get an error message as in the following.
Step 3: Ways to prevent Saving changes is not permitted in SQL Server
There are two ways to prevent the above error.
i ) Alter the table by using Sql Query.
ii) Disable "Prevent saving changes that require table re-creation" option.
Method 1: Alter table by using Sql Query
For example to add new column (Gender) in the existing table (Mas_Employee) use the below script
- Alter table Mas_Employee
- Alter column Gender varchar(40)
Method 2 : Disable "Prevent saving changes that require table re-creation" option to prevent the error.
- Goto Tools, select Options.
- Expand Designers, and select "Table and Database Designers".
- Then uncheck "Prevent saving changes that require table re-creation".
- Click OK.
SubashPosted Aug 20, 2016, 4:41 AM
Good
Santhakumar MunuswamyPosted Jun 17, 2015, 3:53 PM
Nice one