Phil Streiff Blog

Better, faster, cheaper …pick two.

SQL Server 2008 - Management Studio issue

This is a known issue with SQL Server 2008, that certain DDL operations like ALTERing a column datatype using Management Studio GUI fails.

For example, in Object Explorer, navigate to a table column > right-click on column > Modify. Change column datatype or length, then save and this error message displays:

To workaround this problem, go to Query Editor and issue the following DDL statement instead:


ALTER TABLE dbo.ftpfile
ALTER COLUMN cmdline VARCHAR (100);
GO 

 

The column change is successfuly applied now.

Legacy Comments


Marcos
2010-04-19
re: SQL Server 2008 - Management Studio issue
You can also disable this option:

Tools -> Options ->Designers

And uncheck: Prevent saving changes that requiere recreation

Cheers

Phil
2010-04-19
re: SQL Server 2008 - Management Studio issue
Thanks for that tip Marco. I wasn't aware of that option.

Still seems a bit misleading for MS to classify a change in column datatype as one which "requires table re-creation", when it really doesn't, but whatever.

Thanks,
Phil


eyechart
2010-04-19
re: SQL Server 2008 - Management Studio issue
phil,

check the DDL created, you will notice that SSMS does a drop/recreate for this operation instead of issuing an ALTER command.



-ec

Vampal
2010-05-06
re: SQL Server 2008 - Management Studio issue
Thanks for that tip

Arc
2011-06-01
re: SQL Server 2008 - Management Studio issue
Thanks Marcos !! You saved my day :)