CODEWITHSUNDEEP
sqlviewsql-server-2012
Agent007
Agent007

Reputation: 2750

View definition is not getting updated in SQL Server

I have a fairly complex view, generated using SQL Server Migration Assistant for Oracle, but with some logical as well as some syntax errors.

But, when I update the SQL statement of it (with right click -> design and then update the sql in text-area), the new SQL is shown when I right click -> Script view as -> Create/Alter to, but when I open the view design with right click -> design, it does not show the updated SQL statement.

And since the original view definition has syntax errors, I am getting error : SQL text cannot be represented in the grid pane and diagram pane.

I have tried restarting the SQL instance and also with sp_refreshview <ViewName>, but with no success.

I am using SQL Server 2012 Enterprise (64-bit) on Windows Server 2008 R2 (64-bit).

Any help would be greatly appreciated.

Upvotes: 3

Views: 1964

Answers (1)

Ben
Ben

Reputation: 35613

The visual designer is not capable of visualising every construct which can be used in a view. Syntax errors will also cause this error.

If you get this error, your only option is to not use the visual designer, but edit the SQL by hand.

You can do this using 

Right-Click -> Script View As -> ALTER to -> New Query Editor Window

Once you have corrected the errors, and can successfully run ALTER VIEW, you may be able to use the designer.

Upvotes: 7

Related Questions