SQL Query Not Working in Report

I have a very simply SQL query that is not working in a Report but is working in SSMS. Here is the code:

SELECT 
	ComponentID
FROM SolarWindsOrion.dbo.APM_ComponentSetting

Orion Report tool is saying "Query is not valid".

BTW: This does not work for any of the fields in that table, individually or all together.

Any ideas?

THANKS

Mike

Find more posts tagged with

sql query

report

Accepted answers

All comments

wluther

@Mike_Lomax Do you still get an error if you remove the "SolarWindsOrion.dbo." part?

SELECT ComponentID FROM [APM_ComponentSetting]

And, not to be silly, but you are running it as SQL and not SWQL, yes?

Mike_Lomax

I get the same error when running the SQL query within the Report whether I include "SolarWindsOrion.dbo." or not:

KMSigma.SWI

You really shouldn't be using SQL in reports if at all possible. You should be using SWQL. There's a (very real) chance that we'll update the underlying database schema at some point. If that's the case, then pure SQL queries may stop working, but SWQL will continue to work for a long time before being deprecated.

SELECT [Components].ComponentID
--   , [Components].Name
--   , [Components].ShortName
--   , [Components].ComponentType
--   , [Components].ComponentEvidenceType
--   , [Components].ApplicationID
--   , [Components].TemplateID
--   , [Components].ComponentName
--   , [Components].DetailsUrl
--   , [Components].FullyQualifiedName
--   , [Components].UserDescription
--   , [Components].UserNotes
--   , [Components].ComponentOrder
--   , [Components].ApplicationItemID
--   , [Components].Description
--   , [Components].UnManaged
--   , [Components].UnManageFrom
--   , [Components].UnManageUntil
--   , [Components].STATUS
--   , [Components].StatusDescription
--   , [Components].Disabled
--   , [Components].StatusLED
--   , [Components].IMAGE
--   , [Components].AncestorDisplayNames
--   , [Components].AncestorDetailsUrls
--   , [Components].StatusIconHint
--   , [Components].DisplayName
--   , [Components].InstanceType
--   , [Components].Uri
--   , [Components].InstanceSiteId
FROM Orion.APM.Component AS [Components]

What verison are you running?

Mike_Lomax

Kevin,

SQL Queries is what I am more familiar with and have used for the past 11 years in Reports. The option is still available to use them so why are you saying we should avoid them? (((Sorry... I do see now why you said we should avoid SQL Queries.)))

My Orion environment is on 2023.2.1.

Mike