Here’s another silly one but it catches me every time. So I decided to write a quick post about it, to never forget about it again!
Ever seen this warning message while using the Management Studio?
All I did was open the AdventureWorks.Production.Product table in the designer, moved the ListPrice column above the StandardCost column and hit CTRL+S to save changes. Instead of changing the table, it tells me:
Saving changes is not permitted. The changes you have made require the following tabled 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.
What a message huh?! It needs to re-create the table, so what, go ahead! Hang on, even though this is just a Warning, there’s no Continue button! Ow come on, are you kidding?
Like any good developer that wants to get some work done, you stop reading such a long message after the second line and try to find a solution for your problem. Too bad, you should have just continued reading the warning message because the solution is actually hidden in there!
If you open up the Options screen from the Tools menu in SSMS, and then drill down to the Designers > Table and Database Designers node, you’ll see an option called Prevent saving changes that require table re-creation. And by default, that option is checked. Remove the check from that box and you’ll be able to use the table designer as it was intended.
So, here’s your ADSOTD (Annoying Default Setting Of The Day)!
Small note: be careful when you want to use this feature on a large table. The script that gets generated in the background to make the changes needs to copy all records from the old table into the new one and that will obviously require some time!
Have fun!
Valentino.
Be careful when you use the Change Connection button on an open window: it messes up the coloring. More precisely, it will keep the color of the previous connection.
