[solved] Wrapping SQL-query into XML PATH and Incorrect syntax error

omavel

Hi guys!
Since SW still can't provide a convenient way to use sql/swql-queries with email actions, we have to use hacks like that (thank you, mesverrum)

both issues were solved

#1
So we had this SWQL query to gather some components info:

SELECT
n.Caption AS Node,
ccs.ApplicationID,
a.Name AS AppName,
ccs.ComponentID,
c.ComponentName,
cs.ErrorMessage AS Message,
cs.ComponentStatisticData AS Statistic
FROM Orion.APM.Component(nolock=true) c
JOIN Orion.APM.CurrentComponentStatus(nolock=true) ccs ON c.ComponentID = ccs.ComponentID
JOIN Orion.APM.Application(nolock=true) a ON c.ApplicationID = a.ApplicationID
JOIN Orion.Nodes(nolock=true) n ON a.NodeID = n.NodeID
JOIN ORION.APM.CurrentStatistics(nolock=true) cs ON c.ComponentID = cs.ComponentID
WHERE ApplicationID = '1063' AND ComponentName like '%DSA%'

Which was successfully converted to SQL:

SELECT
n.Caption AS Node,
ccs.ApplicationID,
a.Name AS AppName,
ccs.ComponentID,
c.ShortName,
cs.ErrorMessage AS Message,
cs.ComponentStatisticData AS Statistic
FROM APM_Component c
JOIN APM_CurrentComponentStatus ccs ON c.ID = ccs.ComponentID
JOIN APM_Application a ON c.ApplicationID = a.ID
JOIN Nodes n ON a.NodeID = n.NodeID
JOIN APM_CurrentStatistics cs ON c.ID = cs.ComponentID
WHERE ccs.ApplicationID = '1063' AND c.ShortName like '%DSA%'

For me problem is to wrap it into XML PATH, there are a lot of "Invalid column name" errors in the DB manager, obviously I did something wrong:

#2 

Anyway, after simulation, SW shows me another error: "MACRO SQL ERROR - Incorrect syntax near ')'", don't even know why, I use this code:

${SQL: SELECT ISNULL((
SELECT 
n.Caption AS Node,
ccs.ApplicationID,
a.Name AS AppName,
ccs.ComponentID,  
c.ShortName, 
cs.ErrorMessage AS Message,
cs.ComponentStatisticData AS Statistic
FROM APM_Component c 
JOIN APM_CurrentComponentStatus ccs ON c.ID = ccs.ComponentID 
JOIN APM_Application a ON c.ApplicationID = a.ID
JOIN Nodes n ON a.NodeID = n.NodeID
JOIN APM_CurrentStatistics cs ON c.ID = cs.ComponentID
WHERE ccs.ApplicationID = ${N=SwisEntity;M=ApplicationID} 
AND c.ShortName like '%DSA%'
FOR XML PATH('')),'None')}

Same code works fine in the DB Manager:

Could you help me please?

omavel

Shame on me guys, I was so clumsy, of course table names must be without those square brackets.

Fixed:

SELECT ISNULL((
SELECT
cast(cast(n.Caption as nvarchar(max)) + '' as XML),
cast(cast(ccs.ApplicationID as nvarchar(max)) + '' as XML),
cast(cast(a.Name as nvarchar(max)) + '' as XML),
cast(cast(ccs.ComponentID as nvarchar(max)) + '' as XML),
cast(cast(c.ShortName as nvarchar(max)) + '' as XML),
cast(cast(cs.ErrorMessage as nvarchar(max)) + '' as XML),
cast(cast(cs.ComponentStatisticData as nvarchar(max)) + '' as XML)
FROM APM_Component c
JOIN APM_CurrentComponentStatus ccs ON c.ID = ccs.ComponentID
JOIN APM_Application a ON c.ApplicationID = a.ID
JOIN Nodes n ON a.NodeID = n.NodeID
JOIN APM_CurrentStatistics cs ON c.ID = cs.ComponentID
WHERE ccs.ApplicationID = 1030
AND c.ShortName like '%DSA%'
FOR XML PATH('')),'None')

But the second issue is still actual