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.
