Cannot ALTER TABLE in SQL2008/SQL2008 R2

Question

I am working wiith databases in SQL 2008 and in SQL 2008 R2.  If there is a problem with a table definition, say, I need to add or rename a column, I have to drop it and completely recreate it.  I cannot simply modify the table in the Management Studio or in script as in previous versions of SQL Server.

Is there a way around this?  Why was this done?

Thanks,
Eagle

Answer 1

Hello Eagle

Table recreation will happen when the user performs add column or modify column operation using Table designers by default. This is a limitation in SQL Server Management Studio.

The work around is to use T SQL for such operations in the query designer.

eg : ALTER TABLE ADD COLUMN c INT

If you want recreate to be prevented with an error use this article.

-Sreekar

Answer 2

Hello,

Just a little add-in to the clear response of SREEKAR.
SMO is more strict than SSMS , so it rejects every modification of a table which would need a recreation of a table.
I have done this discover when with SSMS , i have tried to modify a table and i decided to have a look on the real script used by SSMS : it is using a temprory table to store the data of the "old" table , deleting the table, recreating it and loading the data from the temporary tble in the table "new format".
Amusing : i remember that a similar question was asked 2 years old and i was asked to have a look on the real script used by SSMS.
SMO does not have this option of blocking a table modification in SMO ( in case of a needed recreation ) , so SMO fails.
That's logical

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

Hi,

 

Please read this article:

http://troubleshootingsql.wordpress.com/2009/12/30/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"

It worked for me.

Answer 4

Hello,

Just a little add-in to the clear response of SREEKAR.
SMO is more strict than SSMS , so it rejects every modification of a table which would need a recreation of a table.
I have done this discover when with SSMS , i have tried to modify a table and i decided to have a look on the real script used by SSMS : it is using a temprory table to store the data of the "old" table , deleting the table, recreating it and loading the data from the temporary tble in the table "new format".
Amusing : i remember that a similar question was asked 2 years old and i was asked to have a look on the real script used by SSMS.
SMO does not have this option of blocking a table modification in SMO ( in case of a needed recreation ) , so SMO fails.
That's logical

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Thanks a lot for you little add-in :). It's really specific enough for me to learn the difference between SSMS and SMO. Thank you!

Answer 5

I feel this was done by MS Design Team to make our lives harder and less productive as DBA's.....

---

RJ Samp

Answer 6

Hello RJ Samp,

I have never heard about this MS Design Team ( but i will do some researches about it ), but i think that your reply is a little too much bitter. According to me, there is a big difference between SSMS which is interactive and SMO which is often used thru an application or thru PowerShell scripts which both are (too) often launched without any deep tests and it is often too difficult to foresee the results on an error or exception ( when they are treated ) thrown in a program or a script. For me, it is only a question of security.

I am using SMO since the SP1 of SQL Server 2005 ( before , SMO was really buggy ), and i prefer an error/exception rather a problem on the structure of a table when i am doing an error. According to my own experience ( and informations given by good MSFT like Jens Suessmeyer or Michiel Worries who learnt me SMO ), SMO generates a script and executes it after having checked its correctness. You can "capture" with the CaptureSql  class

 http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.capturedsql(v=SQL.100).aspx

and the SqlExecutionModes enumeration

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.sqlexecutionmodes(v=SQL.100).aspx

When i am writing a SMO application, i am always using these 2 objects to check whether the script is correct before any testing : i compare the hoped ( and already tested ) script with the script returned by SMO before any "real" execution of the SMO script.

The only one thing that we might reproach to the MS SMO developers is they have tried to block any further problem , they are too careful , it seems to me a not common reproach for a Microsoft team , for me it is a real greeting ...

Have a nice day 

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 7

while I have no idea why it was done.  Ricardo was right on the money.  just change the default setting and I am good to go.

thanks Ricardo you just saved me a ton of time.!!!!!!!

---

Peter G. Simmon

Answer 8

Hello ,

I have marked the post of Ricardo as the good answer after the last post of Peter Simmon ( it is late maybe )

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 9

Thanx Its working ..

:)

Answer 10

Hi Ricardo,

Thanks for this option in Management Studio. It worked also for 2012 version!