SQL SERVER – FIX MANAGEMENT STUDIO ERROR

SQL SERVER – FIX MANAGEMENT STUDIO ERROR.

SQL SERVER – FIX MANAGEMENT STUDIO ERROR

Intoduction :-

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 the option Prevent saving changes that require the table to be re-created.

How to get rid of this problem? 

Explanation :-

First of all let us see the default system structure that SQL SERVER use to have just after spick-and-span installation.

 This feature can be decipher by going to Menu >> Tools >> Options >> Designers >> Table and Database Design >> “Prevent Saving changes that require table re-creation”

The tectonic brainteaser with the use of design mode of SSMS is that, sometimes it adds exorbitant overhead to the original code and locks up the comprehensive database. In the earlier versions of SSMS, it was quite quotidian to have this type of problem even on doing a minimal remodeling the exclusive SSMS got logged untill the operation was over. The reason why SSMS got logged is that when we do perform any remodeling of the table design, it has to perform much more functions than we original conjecture.

The functions that are performed by enginous SSMS are (assumed column’s datatype change over here):-

• Transaction starts.
• When a column’s datatype is changed, SSMS renames the existing table to temp table.
• Then creates a new table with new specification.
• At this point SSMS is having two tables: copy of  previous table with data and empty table with modified structure.
• Now, SSMS starts moving old data from old table to new table.
• If any error occur at this point, it rolls back transaction.
• Else if there is no error, it commits all the operations.
• Transaction ends.

This entire operation of remodeling is too much resource intensive and puts lock on SQL Server tables, eventually logging SSMS.

One Example for better understanding of the senario:-

For example we will now examine an operation (datatype change over here) where we will:-

1)       Create a table with only 3 cloumn.

2)       Add some data to the table.

3)       Add a new type of data to the table (will have error).

4)       Try to edit a column’s datatype from “BIGINT” to “VARCHAR (50)”- (will have error).

5)       Solving the errors.

6)       Final data entry to the table to test it.

So, lets begin…

1)       Create a table with only 3 cloumn.

 USE [Test_DB]
GO
CREATE TABLE [dbo].[Test_Tbl_1](
      [Name] [varchar](50) NULL,
      [Age] [int] NULL,
      [Mobile] [bigint] NULL
) ON [PRIMARY]
  GO 

Notice the third (3^rd) column “Mobile” its datatype is set to “bigint”.

Snapshot has been provided.

2)       Add some data to the table.

--1 
INSERT INTO [Test_DB].[dbo].[Test_Tbl_1]
           ([Name],[Age],[Mobile])
     VALUES (('RAHUL'),(1),(9988888888))          
GO

--2
INSERT INTO [Test_DB].[dbo].[Test_Tbl_1]
           ([Name],[Age],[Mobile])
     VALUES (('XYZ'),(7),(8882991988))
GO

--3
INSERT INTO [Test_DB].[dbo].[Test_Tbl_1]
           ([Name],[Age],[Mobile])
     VALUES (('EXAMPLE DATA'),(0),(9999999999))
GO

Snapshot has been provided.

3)       Add a new type of data to the table (will have error).

--1
INSERT INTO [Test_DB].[dbo].[Test_Tbl_1]
           ([Name],[Age],[Mobile])
     VALUES (('2nd EXAMPLE DATA'),(99),('+91 1234567890'))        GO

Will this query work?

No!! It won’t…………….

Particularized snapshot in design mode of SSMS has been provided.

4)       Try to edit a column’s datatype from “BIGINT” to “VARCHAR (50)”- (will have error).

The error came up because of the table re-creation is not permitted, which is the by default setting of SQL SERVER.

5)       Solving the errors.

Un-checking the checkbox of “Prevent saving changes that require table re-creation”, will permit the table to be re-created if any modification is done in the design of the table.

6)       Final data entry to the table to test it.

--1 
INSERT INTO [Test_DB].[dbo].[Test_Tbl_1]
           ([Name],[Age],[Mobile])
     VALUES (('2nd EXAMPLE DATA'),(99),( '+91 1234567890'))
GO

Will this quer work?

Yes!! It work…………………………..

Particularized snapshot in design mode of SSMS has been provided.

The data is saved successfuly sans any problem, to substantiate that we can use:-

use Test_DB
select * from dbo.Test_Tbl_1

We will get

Conclusion:-

The problem of SSMS in design phase is unriddle. The single checkbox can turn thing worst for a developer.  Sometimes TSQL alone can’t solve out each and every problem, design is also needed.

Thanks……….