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"

Comments

24 responses to “Cannot modify table column in SQL Server 2008 using Table Designer”

  1. Kimpheng Avatar
    Kimpheng

    Thank you it work…..

    Like

  2. Himanshu Patel Avatar
    Himanshu Patel

    Thanks, It helped.

    Like

  3. truongdothienhoang Avatar

    Thx you , great post

    Like

  4. WorkingMom Avatar
    WorkingMom

    Thanks a lot!

    Like

  5. Kiran Avatar
    Kiran

    Thanks, Its working now.

    Like

  6. khurramengr Avatar

    thanks alot……….

    Like

  7. buntha Avatar
    buntha

    Thanks you big big, that good answer

    Like

  8. Bunthadrsys Drsys Avatar

    Thanks you big big that is good answer

    Like

  9. Bunthadrsys Drsys Avatar

    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

  10. Amit Banerjee Avatar

    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

  11. Sunil Kumar Avatar
    Sunil Kumar

    Thanks a lot!!!!!

    Like

  12. mohammad Avatar
    mohammad

    thanks likeeeeeeeeeeeeeeee

    Like

  13. seo|vps|hosting|dedicated|server…

    […]Cannot modify table column in SQL Server 2008 using Table Designer « TroubleshootingSQL[…]…

    Like

  14. udham singh Avatar
    udham singh

    Thanks, It worked.

    Like

  15. ARUNKUMAR.R Avatar
    ARUNKUMAR.R

    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

  16. Amit Banerjee Avatar

    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

  17. bb Avatar
    bb

    thank you for heip

    Like

  18. Hannod Avatar
    Hannod

    thank you for help.

    Like

  19. A year that was « TroubleshootingSQL Avatar

    […] Cannot modify table column in SQL Server 2008 using Table Designer […]

    Like

  20. Ami Avatar
    Ami

    Thank a lot…

    Like

  21. NightMare Avatar
    NightMare

    so thank you

    Like

  22. kk Avatar
    kk

    thanQ sir…………

    Like

  23. Faizan Shaikh Avatar
    Faizan Shaikh

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

    Like

  24. pri Avatar
    pri

    Thanks, It worked.

    Like