Monday, February 1, 2010

New Behaviour When Modifying SQL Server tables from Management Studio

Today I struck some behaviour I'd not seen before. I was attempting to change the nullability of a column from inside Management Studio when up popped a message with the following text:

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 requie the table to be re-created.

Hmm, now I realise that this type of change needs the drop and re-create of the table, but I'm sure in earlier versions of SQL Server this type of change was possible from the designer with the extra work being taken care of silently in the background. Now, in 2008, it seems to be blocked entirely - ideally I'd have liked to be able to make the change in the designer and then generate the change SQL script from there rather than having to write the script myself from scratch.

So, I did a little digging looking for the option mentioned in the error mesage. I found it under the Tools->Options menu in the Designers section.

Simply uncheck the box and you'll be able to make these types of table changes and / or generate the change scripts from Management Studio.

No comments:

Post a Comment