0 Replies Latest reply on Jul 28, 2016 9:09 AM by markobrien

    Create a SQL View in Database Manager

    markobrien

      I am trying to build a view in SQL (note - NOT an Orion View) through queries in the Database Manager. When I enter the following query:

       

      SELECT CustomPollerStatistics.RowID, CustomPollerStatistics.Status AS NeighborLinkAddr, CustomPollerAssignmentView.NodeID, CustomPollerStatistics.DateTime

      FROM CustomPollerStatistics

      INNER JOIN CustomPollerAssignmentView

      ON CustomPollerAssignmentView.CustomPollerAssignmentID=CustomPollerStatistics.CustomPollerAssignmentID

      WHERE CustomPollerAssignmentView.CustomPollerName='ospfNbrIpAddr'

       

      I get the table that I expect as a result. However, when I attempt to make a view from the query as follows:

       

      CREATE VIEW OSPFNbrLink AS

      SELECT CustomPollerStatistics.RowID, CustomPollerStatistics.Status AS NeighborLinkAddr, CustomPollerAssignmentView.NodeID, CustomPollerStatistics.DateTime

      FROM CustomPollerStatistics

      INNER JOIN CustomPollerAssignmentView

      ON CustomPollerAssignmentView.CustomPollerAssignmentID=CustomPollerStatistics.CustomPollerAssignmentID

      WHERE CustomPollerAssignmentView.CustomPollerName='ospfNbrIpAddr'

       

      I get this in the "Messages" tab:

       

      Msg 111, Level 15, State 1, Line 1

      'CREATE VIEW' must be the first statement in a query batch.

      Msg 111, Level 15, State 1, Line 1

      'CREATE VIEW' must be the first statement in a query batch.

       

      In a search, I found that preceding the expression with "exec" and wrapping the expression with ('') will avoid this message. I enter:

       

      exec

      ('CREATE VIEW OSPFNbrLink AS

      SELECT CustomPollerStatistics.RowID, CustomPollerStatistics.Status AS NeighborLinkAddr, CustomPollerAssignmentView.NodeID, CustomPollerStatistics.DateTime

      FROM CustomPollerStatistics

      INNER JOIN CustomPollerAssignmentView

      ON CustomPollerAssignmentView.CustomPollerAssignmentID=CustomPollerStatistics.CustomPollerAssignmentID

      WHERE CustomPollerAssignmentView.CustomPollerName='ospfNbrIpAddr'')  NOTE - those are two single asterisks at the end of ospfNbrIpAddr, not a single closing quotation mark

       

      And the "Message" tab says:

       

      Msg 102, Level 15, State 1, Line 7

      Incorrect syntax near 'ospfNbrIpAddr'.

      Msg 102, Level 15, State 1, Line 7

      Incorrect syntax near 'ospfNbrIpAddr'.

       

      So, progress, I guess (An issue shows up on Line 7 instead of Line 1).

       

      Does anyone know why the syntax is incorrect when trying to make this view when it is just fine as a simple query?

       

      Thanks in advance.