«

»

Apr 09

Saving changes is not permitted in SQL Server

SQL Server Management Studio does not allow you to save changes to a table which require table re-creation such as changing data type for a column. When you perform such changes you will run into following error message:

save1

Here, I have tried changing data type for First’Name’ column from NVARCHAR(50) to VARCHAR(50). Since this requires table re-creation Management Studio does not allow this operation to be completed, and it is doing so for your own good!

This activity can be completed without re-creating table if you are using T-SQL. Following T-SQL code can be used to avoid table re-creation:

USE[Naseer]

GO

 

ALTERTABLE[dbo].[Employee]

ALTERCOLUMN firstName varCHAR(50)

GO

The above statement will change the data type of the ‘FirstName’ column to VARCHAR(50) without having to re-create table.

We are better off with this option enabled. Although, if you are a GUI fan you can disable this safety net.

1. Go to Tools > Options

2. Go to Designers > Table and Database Designers

3. Uncheck Prevent saving changes that require table re-creation.

image3

Enjoy Coding!!!!

Admin

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>