up vote 2 down vote favorite
share [g+] share [fb]

I have added a column to a table in SQL Server 2005. When I attempt to save it, I get this message:

Saving changes is not permitted. The change 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.

The column I added was nothing special, a nullable nvarchar(11) with no index or constraints. I have never seen this problem before, and have changed the table many times earlier.

Anyone know what's going on, and where I should look for the mentioned option?

link|improve this question
80% accept rate
feedback

2 Answers

up vote 3 down vote accepted

Found it.

It was actually an option in SQL Server Management Studio 2008 (which I am using, even though the DB in question is 2005), and not in the DB or server instance as I expected.

The option is exactly as stated in the error message "Prevent saving changes that require the table to be re-created". I found it in Management Studio under Tools -> Options -> Designers, and it is obviously enabled by default. A very strange default indeed!

link|improve this answer
3  
They added it because this actually results in Management Studio selecting the data in the table into a temp table, dropping the table and recreating it, then reinserting the data back in from the temp table. Not ideal in many situations. – squillman Sep 16 '09 at 18:24
Yeah, I can see that, I guess. – Tor Haugen Dec 22 '09 at 23:53
feedback
up vote 1 down vote

It is probably coming up because you aren't putting the column at the end of the table. Putting a column in the middle of the table causes the table to be dropped and recreated.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.