Tuesday, November 11, 2008

Sql Management Studio : Prevent saving changes that require the table to be recreated

When working with the table designer in Microsoft Sql Server Management Studio 2008, I tried to save a table I modified, and received this 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 recreated or enabled the option Prevent saving changes that require the table to be re-created.



Enabling the option to allow you to complete these types of actions is pretty easy, from the "Options" menu, select the "Designers" tab, and the uncheck the "Prevent saving changes that require table re-creation". Then click "OK"


Problem solved. Hey Microsoft: It would be great if this feature would allow for three levels: On, Off, and Warn.

1 comment:

katman26 said...

It's frustrating that the default option is "On" for that feature, since there's been nothing like that in previous versions. I agree that there should be three different settings, with the default being "Warn".

Though, the coolest thing about those options is the option 4 above that one - "Auto generate change scripts". Now, whenever you make a designer change in MS and save, you're presented with a dialog containing the SQL script used to make the changes you just saved. That is a major help, especially when trying to script out changes from development to production. I wonder what RedGate thinks of that.