MSSQL Server Studio Corrupts View

[Galaxiom]

When I use a CTE in a View, and open it in Design mode, MSSQL Server Studio 2008 says it cannot represent the SQL text in the grid and diagram panes.

That wouldn't matter except it and strips out the first of the parts it can't handle (WITH whatever AS), turning perfectly valid SQL into rubbish. Even after this it still can't handle the SQL text in the grid anyway so why doesn't it just leave it alone?

Usually all I want to do is type some trivial alteration.

Is there way to have the Designer just leave my SQL text alone. Otherwise it seems I have no alternative but to add the stripped part back in each time or script my changes.

 

[ButtonMoon]

I never use the Designer and I don't know anyone who does. The SQL Server Designer window is not nearly as bad as its equivalent in Access but it's still more trouble than it's worth IMO.

An easy way to edit a view definition is to right-click the view in the Object Browser in Management Studio and then select the option to script it. Even simpler, just open the source code in Management Studio from a file or from source control. You do save your source code scripts don't you?

 

[kevlray]

Make sure the MS-SQL database is actually at least 2005 (our DB staff likes to mix versions on one SQL server).