Cannot modify table column in SQL Server 2008 using Table Designer


There is a new option added in SQL Server 2008 for modifying tables while using the Table Designed in Management Studio which will prevent saving any changes to a table that needs a drop/re-create of the table. The error that you would see in the Management Studio is:

TITLE: Microsoft SQL Server Management Studio
——————————

User canceled out of save dialog
(MS Visual Database Tools)

If you want to modify a table property through the UI which requires a table recreation, then you need to change the following options in Management Studio:

Tools ->Options-> Designers -> Table and Database Designers -> uncheck the option "Prevent saving changes that require table re-creation"

Advertisements

24 thoughts on “Cannot modify table column in SQL Server 2008 using Table Designer

  1. I want to alter table by script in sql express 2008 but then (->right click) on table then select on (->script table as) and then (->alter to). but (alter to) it show in invisible. how can i do
    please help thanks

    Like

  2. Do you have ALTER TABLE permissions on the table? And is the option displayed for all tables in all databases? If not, then it is most likely a permissions issue.

    Like

  3. Pingback: seo|vps|hosting|dedicated|server

  4. ACTUALLY I CREATED A TABLE IN SQL. IN THAT THE COLUMN NAMES ARE RENT, MOTOR_BILL, CURRENT_BILL AND TOTAL_BILL.

    I GAVE VALUES TO THE RENT, MOTOR_BILL, CURRENT_BILL……….

    NOW I WANT THE AUTOMATIC CALCULATION FOR TOTAL_BILL. BY USING ALTER or UPDATE or MODIFY.

    IS THERE ANY POSSIBILITIES MEANS PLEASE TELL THE SOLUTION……………..

    ARUNKUMAR.R

    Like

  5. You can do this using UPDATE statement or create a computed column for your table. I would suggest looking up “computed columns” and “update” in SQL Server Books Online.

    Like

  6. Pingback: A year that was « TroubleshootingSQL

  7. If you want to modify table….? goto TOOLS = OPTIONS = DESIGNERS = TABLE AND DATABASE DESINGNERS.then unchecked Prevent saving that require table re-creation….

    Like

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s