Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Warning of Saving Changes in not permitted in SQL

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 241
    Comment on it

    Warning: "Saving Changes in 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 cant be re-created or enabled the option Prevent saving changes that require the table to be re-created"

        This is the warning which I faced recently while saving the alterations to the existing table in the design mode.On doing R&D I got the actual process SQL Server Management Studio performs, which is as follows-

    Incase we are adding a new column to the existing table, the following operations takes place:

    1. Transaction starts.
    2. When a new column is added SQL Server Management Studio renames the existing table to temp table.
    3. After that, it creates a new table with new specification.
      (Now, SQL Server Management Studio has two tables, copy of previous table with data and empty table with new structure.)
    4. SQL Server Management Studio now starts moving older data from older table to newer table.
    5. If there is any error, it rolls back transaction.If there is no error, it commits all the previous operations.
    6. Transaction ends.

    As this entire operation is very resource intensive and puts lock on SQL Server tables in operation, eventually hanging SQL Server Management Studio, therefore SQL Server Management Studio prevents user from running any operation that goes through the above-mentioned process.

    But this feature causes problem for someone in saving the table changes. Therefore inorder to avoid this feature we can make changes in the SQL Server Management Studio as follows:

    Menu >> Tools >> Options >> Designers >> Uncheck Prevent Saving changes that require table re-creation.

    Explanation:-

    First go to- Menu then click Tools then click Options then click Designers in the navigation Menu corresponding to this Uncheck the option "Prevent Saving changes that require table re-creation"

    Now we can easily save the changes made..!

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: