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 12:06:00 PM 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 5:15:12 PM)
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 (no comments yet)

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:

Blogs RSS Feed

BrianKnight's latest blog posts

  • SQL Server 101 Webinar Series
    2/22/2010 5:11:32 PM
    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 Opens February 1st, 2010 and is limited to the first 1,000 people. Webinars traditionally fill quickly. Date and Time (All times EST) All sessions are free. Session and Speaker * (click book images take you to author bo...
  • SQL Saturday and PASS
    2/2/2010 4:54:40 PM
    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 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 ...
  • SQL Lunch Code - Scripting in SSIS
    1/19/2010 1:27:25 PM
    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 coding examples, which you can see here: Download the code examples !
  • SSAS MDX Year to Date (YTD) Calculation Example
    12/18/2009 9:13:36 AM
    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 dimension or role playing dimension you want to slice this by. After you know that, you can use an MDX calculation as shown below for this:     Sum(YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]) This will show...
  • Watch the BIDN.com Twitter Feed
    12/17/2009 10:38:49 PM
    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 number of ways. We'll first feature your question in our newsletter and it will also be automatically be tweeted to http://www.twitter.com/bidn for any followers. Your question will be featured on twitter with a prefix of "BIDN User SOS" with you...
Blogs RSS Feed

Latest community blog posts

  • Free and Discounted Microsoft Learning Resources
    3/15/2010 11:06:43 AM
    I was browsing the Microsoft Learning portal over the weekend and came across the following free or dicounted learning resources for SQL Server. Free Resources: Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform (1 hour)   Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence (1 hour) Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development (1 hour) Clinic 6258: New Features Of Microsoft SQL ...
  • SQL Agent jobs
    3/14/2010 2:46:26 PM
    Recently I was asked to provide a list of all sql jobs on a certain server running between 2 and 3 in the morning.  We have quite a few jobs and it would be time consuming to look through each to determine the time of day each runs.  So I used the sysjobs, sysjobsschedules tables to return the job name and times the jobs run. There are several jobs related tables in the msdb database: sysjobs sysschedules sysjobhistory sysjobsteps syscategories   The table names are pretty self explanatory.  The...
  • Solving All Versus (Select All) SSRS End User Confusion
    3/12/2010 11:11:39 PM
    When developing reports that use Analysis Services as a data source end user can sometimes be confused about some of the options they see in report parameters.  Anytime you have a parameter that allows for multiple values to be selected then you will see a (Select All) option that Reporting Services adds that make for an easy way to check off each item in the parameter dropdown.   If you followed most of the defaults when developing you dimensions in Analysis Services you likely also have an opt...
  • DTS Runtime Required
    3/12/2010 10:49:40 AM
    Yesterday, I dealt with a client that was having issues converting his dts packages to SSIS 2008. He was using DTS xChange to convert the packages, but each time he tried to run the migration, he received the following error. "Can not load Tasks. Error : Unable to cast COM object of type 'System.__ComObject' to interface type 'DTS.CustomTask'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{10020904-EB1C-11CF-AE6E-00AA004A34D5}' failed d...
  • Missing AdventureWorks2008 Found
    3/12/2010 7:08:28 AM
    So I finally got through all the SSIS Class Labs including the Group Labs and decided to figure out why I didn't have AdventureWorks2008. I had previously tried to enable FILESTREAM according to some blogs I had read about it but had no success. Maybe I just understand all this a little better now but when I exactly followed these steps right out of the help file it worked for me: 1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and t...