patternsqlMinor
view corrupted when viewed using design feature
Viewed 0 times
corrupteddesignviewviewedusingwhenfeature
Problem
All,
I had a very strange thing happen last Friday. We have a table that is updated on a daily basis using a job that fires a proc that utilizes a view. The columns were being transposed from the view to the table being updated i.e. column A from the view was updating column D in the table instead of column A as it should have.
When I right clicked the view and selected "Design" from the menu I could see the problem was indeed that the sql statement generated was being written to update column D in the table with information from column A in the view.
I suspected a change to the view definition so I right clicked the view and scripted it out to see when it was last updated and when I did this the script generated was correct and completely different from the sql generated when I chose the "design" option. At this point I dropped the view and recreated it from the script. Now all is well.
Has anybody ever seen this? I've been working with SQL server since 2000 and have never come across this. We are using SQL2005 SP4 on Win2K3 (upgrading to 12 soon). Any insight would be appreciated.
-Paul
I had a very strange thing happen last Friday. We have a table that is updated on a daily basis using a job that fires a proc that utilizes a view. The columns were being transposed from the view to the table being updated i.e. column A from the view was updating column D in the table instead of column A as it should have.
When I right clicked the view and selected "Design" from the menu I could see the problem was indeed that the sql statement generated was being written to update column D in the table with information from column A in the view.
I suspected a change to the view definition so I right clicked the view and scripted it out to see when it was last updated and when I did this the script generated was correct and completely different from the sql generated when I chose the "design" option. At this point I dropped the view and recreated it from the script. Now all is well.
Has anybody ever seen this? I've been working with SQL server since 2000 and have never come across this. We are using SQL2005 SP4 on Win2K3 (upgrading to 12 soon). Any insight would be appreciated.
-Paul
Solution
Yes, the view designer is a horribly crippled, buggy piece of garbage you shouldn't be using to "design" your views. It should be called the view destroyer, because it can do exactly what you've observed: re-write any non-standard T-SQL into what it thinks you meant. And by "any non-standard T-SQL" I mean anything not predicted by the two developers who likely spent about a day testing this "feature."
Always, always, always modify your views using Script View As > Alter to > New Query Editor Window.
See these related Connect items:
http://connect.microsoft.com/SQLServer/feedback/details/465498/ssms-designer-is-sending-incorrect-alter-statement-to-engine-when-we-have-geomerty-data-type
Where I mention in the workarounds:
Obviously a better approach is to right-click the view, script as alter to new window. I wonder when they'll admit defeat and ditch the visual designers altogether. They cause more problems than they solve, IMHO.
http://connect.microsoft.com/SQLServer/feedback/details/362679/ssms-right-click-on-a-view-to-edit
Where I do something similar, and
http://connect.microsoft.com/SQLServer/feedback/details/459202/no-edit-option-when-right-clicking-on-a-view-in-management-studio-2008
Where I ask:
Didn't Edit bring up the horrible graphic designer for views, which was riddled with bugs and lacked support for several basic T-SQL constructs? Isn't that what Design does now?
Always, always, always modify your views using Script View As > Alter to > New Query Editor Window.
See these related Connect items:
http://connect.microsoft.com/SQLServer/feedback/details/465498/ssms-designer-is-sending-incorrect-alter-statement-to-engine-when-we-have-geomerty-data-type
Where I mention in the workarounds:
Obviously a better approach is to right-click the view, script as alter to new window. I wonder when they'll admit defeat and ditch the visual designers altogether. They cause more problems than they solve, IMHO.
http://connect.microsoft.com/SQLServer/feedback/details/362679/ssms-right-click-on-a-view-to-edit
Where I do something similar, and
http://connect.microsoft.com/SQLServer/feedback/details/459202/no-edit-option-when-right-clicking-on-a-view-in-management-studio-2008
Where I ask:
Didn't Edit bring up the horrible graphic designer for views, which was riddled with bugs and lacked support for several basic T-SQL constructs? Isn't that what Design does now?
Context
StackExchange Database Administrators Q#59477, answer score: 7
Revisions (0)
No revisions yet.