Prevent Saving Changes in SQL 2008 | SansSQL

Friday, November 6, 2009

Prevent Saving Changes in SQL 2008

Prevent Saving Changes is a new option that is introduced in SQL Server 2008. This option helps in preventing the unexpected changes that can happen on a table which includes dropping and re-creating the table.
By default, Prevent Saving Changes Option will set to ON in SQL Server 2008.

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the below error message

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

Demo:
Create a table named Tbl_PreventSavingChanges
            1. Expand the Database AdventureWorks.
            2. Right-Click on the Tables and select New Table





            3. Save the Table

Now change the data type of the column ChangingColumn from nchar(10) to nvarchar(10)


And click on save button and notice an error popping out



Now to overcome this error, first click on cancel button and come out of the error message.
            1. Go to "Options" under the "Tools" menu


            2. In the options, expand “Designers” and select “Table and Database Designers


            3. Now, Uncheck the option “Prevent saving Changes that require table re-creation



            4. Click OK
            5. Save the table. Now you will be able to save the table.

This article is also available in pdf format for downloading.
Please Click here to get your copy.

12 comments:

Unknown said...

Thank you!!! Saved me hours of head-banging.

Anonymous said...

thank you!!

Anonymous said...

thanks

Anonymous said...

THANKS :d

Anonymous said...

thank you!!! a lot!

Anonymous said...

thank you!! very useful text

Ashish said...

Its Great Article very useful

Anonymous said...

Thanks buddy!!!

jackman10 said...

Well thanks to the wonderful work of our local pharmacists they made her medicine into an easily ingested liquid. rxypharmacy

jackman10 said...

This benefits fruits and vegetables in a similar way to what nitrogen does. Online Weed Shop

Huongkvb said...

Mua vé tại Aivivu, tham khảo

Ve may bay di My

mua vé máy bay từ mỹ về việt nam hãng eva

nhật về việt nam

đặt vé máy bay từ đức về việt nam

các đường bay từ canada về việt nam

ve may bay tu han quoc ve viet nam

bảng giá khách sạn cách ly tại hà nội

vé máy bay chuyên gia nước ngoài sang Việt Nam

jackman10 said...

Alterative herbs specifically aid the channels and organs of elimination. They are important for helping the body to rid itself of harmful or useless substances and allow other energy to be spent on supportive processes improving vitality - red clover (Trifolium pratense), burdock (Arctium lappa), Poke root (Phytolacca Americana), Echinacea (Echinacea purpurea), cleavers (Gallium aparine), garlic (Allium sativum), yellow dock (Rumex cripus), violet (Viola odorata), gotu kola (Centella asiatica), nettle (Urtica dioica), figwort (Scrophularia nodosa), sarsaparilla (Smilax spp.) dank vapes northern lights

Post a Comment

Ads