home  »  Blogs  »  BrianKnight  »  SQL Server 2008 Designer Behavior Change: Saving Changes Not Permitted

SQL Server 2008 Designer Behavior Change: Saving Changes Not Permitted

change text size: A A A

posted 11/7/2009 by BrianKnight

I had been working in SQL Server 2008 for a number of months while working on the upcoming 2008 books and never had experienced this issue until just today. In hindsight, I can't imagine how I didn't experience sooner. The behavior change is when you save a table in Management Studio that requires the table be dropped and recreated behind the scenes, the change will fail by default with the below warning. In SQL Server, tables are dropped, recreated and reloaded automatically for you without having to worry about what's going on behind the curtains but this may create issues for some users. This behavior is required upon a number of actions but most common is creating a new column in a specific location in the table.

Warning Message:

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.

image_thumb5

This is by design and can be quickly fixed in Management Studio by unchecking a property. To fix this in Management Studio, go to Tools -> Options then go to the Designer Page and uncheck "Prevent saving changes that require table re-creation".

 

image_thumb3

-- Brian Knight

tags: management studio, sql server 2008
To rate this blog entry please  register or login

Author

BrianKnight BrianKnight (Member since: 10/22/2009)
Brian Knight, SQL Server MVP, MCITP, MCSE, MCDBA, is the owner and founder of Pragmatic Works. He is also the co-founder of SQLServerCentral.com and JumpstartTV.com. He runs the local SQL Server users group in Jacksonville (JSSUG). Brian is a contributing columnist at several technical magazines and does regular webcasts at Jumpstart TV. He is the author of 10 SQL Server books. Brian has spoken at conferences like PASS, SQL Connections and TechEd and many Code Camps. His blog can be found at http://www.pragmaticworks.com.

View BrianKnight 's profile

Comments (1)

  • 12/31/2009 7:11:40 PM by: 
    Thanks for this!

Post a comment

Comment (No HTML)  

Type the characters:
 *
 
   

Join Business Intelligence Developer Network for FREE Today!

It's fast, easy and free! Submit articles, get your own blog, ask questions & give answers in the forums, and become a better developer, faster.

enter your email address:

BrianKnight's latest blog posts

  • SQL Server 101 Webinar Series
    2/22/2010
    February SQL Server 101 Series New to SQL Server or trying to enter the field? This webinar series will walk you through the Microsoft SQL Server stack so that you can become a DBA or Business Intelligence (BI) developer. Registration Open...
  • SQL Saturday and PASS
    2/2/2010
    I wanted to post this letter that just went out from PASS. As it reads, Andy, Steve and I are excited to announce that we have gifted one of our prized brands, SQL Saturday to PASS to grow the brand with the community owning at a whole. Normal ...
  • SQL Lunch Code - Scripting in SSIS
    1/19/2010
    Thanks for all who attended my SQL Lunch session! There was a few questions that reoccured. One was about the free components I showed. The free component was Task Factory and can be downloaded from http://www.pragmaticworks.com. The other was for the cod...
  • SSAS MDX Year to Date (YTD) Calculation Example
    12/18/2009
    Often times, you want to analyze sales using a year to date view as shown below: While you can do this in an MDX formula in SSRS, you might find this calculation valuable enough to store a server-side calculation. To do this, you'll need to pick what date...
  • Watch the BIDN.com Twitter Feed
    12/17/2009
    We're moving fast at the BI Developer Network (BIDN.com). In an effort to make sure you get your BI questions answered, we've installed a new twitter interface that if your question in our forums doesn't go answered after 2 days, it will escallated in a n...

Latest community blog posts

  • BradSchacht SQL REPLACE for editing Configuration Tables
    3/10/2010
    Package Configurations in SSIS are key to making your packaged portable across the entire development process.  The problem you being to face with configuration files is the quantity that you collect.  Recently I was working on a project where we had paca...
  • BradSchacht Calculate the Date of the Previous Sunday
    3/10/2010
    There are a number of ways that you can calculate dates using T-SQL.  A common practice is to find the first day of the week which can be accomplished easily by using the following function.select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)However, you will ...
  • Bevans Back to Basics
    3/10/2010
    Today I figured I go back to basics with this blog since a majority of the visitors to this site are primarily DBA's and Database Guru's. This may turn into a series but for now we will discuss a bit of a feared power of sql server, and that is the fact t...
  • PatrickLeBlanc Using a CTE with a T-SQL Pivot Statement
    3/9/2010
    During a T-SQL class that I was recently teaching I was asked if a PIVOT could be performed using a Common Table Expression (CTE).  In other words, instead of using the method outlined in SQL Server Books Online that uses a derived table, replace the deri...
  • mroman98 Execute a SSIS package from a remote server
    3/9/2010
    Hi ,   I have a package that i need to be able to execute form a remote server....   How can i do that?... i need to have an icon on the desktop so the user can click on it and the package be executed. This is a very simple package where i have s source f...