SQL Report Queries - can use CTEs?

Question

Hi All,

Solarwinds report.  Add content.  Custom table.  Using straight SQL query (not SWQL, just SQL).

Something like this:

with NodesWithDC (NodeID)

AS

(

SELECT NodeID

FROM [dbo].[Nodes]

WHERE Caption like '%-DC-%'

)

SELECT NodeID from NodesWithDC

Note that the "with" and "AS" clauses designate a SQL "Common Table Expression" (CTE).

Preview Results returns "Query is not valid"

This code runs PERFECTLY in SQL Management Studio against the Orion database.

DB is SQL 2014, running on Windows Server 2012 R2 so it definitely supports CTEs.

I'm aware that for my test query I don't really need a CTE.  At the end I want a list of all Domain Controllers (that have "DC" in the name) that are NOT running a certain application.  So, at the end I will join the results of the CTE with another table and use the EXCEPT clause to get my results.

Can someone assist - does Solarwinds SQL report query simply not use Common Table Expressions?

Thanks much.

robert777 · Accepted Answer

I solved my own problem.

Answer: yes, CTEs will work in report queries.

But: you have to put a semicolon ";" at the start of the query.

My best guess for the reason:  CTEs have to run at the start of a batch.  The semicolon ends a batch and permits a new batch to be started.  For those of us who administer Microsoft SQL if you start your query with a CTE the semicolon is assumed so you don't have to type it.  Most likely the SQL version underlying the Solarwinds query processor is a more generic SQL which requires the query to start with a semicolon.

Added a semicolon at the start of my query and it works like a charm.