Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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?

share|improve this answer
1  
+1 "Not using the view designer" is a workaround to a surprisingly large number of issues. –  Jon Seigel Feb 23 at 19:31
    
Aaron,Thanks for the imput. –  Paul_J Feb 23 at 23:02
    
Edit: Aaron and Jon, Thanks for the input, I usually do create all objects by writing the script myself, however this time the "view" corrupted itself. It was returning these errant values before I examined the structure of it using the designer. I should have included the fact the view was dropped and replaced via script in a .NET deployment of an application upgrade. The developers dropped and recreated the view via script but it somehow got corrupted. I really appreciate the help and now know I'm not going crazy. I'm glad other people were there because I would not have believed it! THX AGN –  Paul_J Feb 23 at 23:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.