Thursday, April 23, 2009

SQL Server 2008 - Can't Save Table Changes

I am just playing around with a new image at the moment that has BizTalk 2009, SQL 2008 and Visual Studio 2008.  

I thought that in order to try out some of the features of BizTalk 2009 that I would make a sample solution that contained a WCF service, BizTalk orchestration and associated schemas for calling the service and mapping the result.

I just happened to have forgotten a field in a data table behind my service.  Not to worry I thought, just add the column and then regenerate the LINQ .dbml.  However, my table has timestamps and audit stuff like that in the last columns and I wanted to put my new data field further up, so using the UI on SQL Management Studio 2008 I duly inserted a column, set the properties and clicked on save.  What I got was the following error in a dialogue:

"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."

Bugger.  What can the matter be?  Why can't I even save a table?  It turns out that there is a setting in SQL Server Managemet Studio that won't allow table changes if they result in a recreation of the table.  Note that I inserted a column between existing columns in the database.  For years now, behind the scenes SQL Server has generated a script that removes constraints, renames the current table, creates the new version of the table and then bulk loads the data into it, finally dropping the original (and now renamed) table.  Of course, when you have a production system this sort of script can be a very dodgy thing to run while the database is operating as you lose referential integrity while the script is running.  Hence it is a "good thing" for it to be disabled by default.  However, for a dev environment, especially one where I am doing a proof of concept....nah.  No thanks.

Have a look at this post on the simple steps on how to resolve.


Fabio said...

Thanks for the tip. Saved my day

Anonymous said...

This is a risk to turning off this option. You can lose changes if you have change tracking turned on (your tables).