SQL Server Discovery

Better, faster, cheaper ...pick two.
posts - 13, comments - 80, trackbacks - 0

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:

 photo cantsavecolaltererror_zps286006e7.png

 
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.

Print | posted on Monday, April 19, 2010 12:51 PM |

Feedback

Gravatar

# 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
4/19/2010 1:39 PM | Marcos
Gravatar

# 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

4/19/2010 7:03 PM | Phil
Gravatar

# 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
4/19/2010 11:53 PM | eyechart
Gravatar

# re: SQL Server 2008 - Management Studio issue

Thanks for that tip
5/6/2010 10:44 PM | Vampal
Gravatar

# re: SQL Server 2008 - Management Studio issue

Thanks Marcos !! You saved my day :)
6/1/2011 9:11 AM | Arc
Gravatar

# re: SQL Server 2008 - Management Studio issue

The Indianapolis Jiu Jitsu website that I work on runs off of SQL Server 2008 so this blog has been a life saver for me when I run into issues. I actually had the same problem mentioned in the post and, thanks to you all, I am back up and running. A day with the sight down is a day that nobody can discover the academy.
2/17/2012 6:12 PM | Jerry2
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET