with [CTE] as (select ID,name,PARENT_ID,TYPE_ID as 'Type' from VPX_ENTITY c where c.[Parent_Id] ='xxx' union all select c.ID,c.NAME,c.PARENT_ID, c.TYPE_ID from [CTE]p, VPX_ENTITY c where c.[Parent_Id] = p.[Id] and c.[Parent_Id] <> c.[Id]) Select a.power_state as 'Status','DC01'as 'Datacenter', UPPER('" & (rowVal) & "') as Acronym, a.id as ID, f.NAME as HostName,a.IP_ADDRESS as 'IPAddress',c.GUEST_FULL_NAME as OS, a.NUM_VCPU as CPU,cast(a.MEM_SIZE_MB as integer) as 'Memory', sum(round((((cast(b.HARDWARE_DEVICE_CAPACITY_IN as float)/1024)/1024)),3)) as 'Storage'
from vpx_vm a join vpx_virtual_disk b on a.id = b.vm_id,VPX_VM_CONFIG_INFO c, VPX_ENTITY f, [CTE] g where a.Is_template = 0 and c.ID = a.ID and
f.ID = a.ID and g.Type = '0' and g.ID = a.ID Group by a.DNS_NAME,a.IP_ADDRESS, a.NUM_VCPU,a.IP_ADDRESS, c.GUEST_FULL_NAME,f.NAME, a.MEM_SIZE_MB,a.power_state,a.id ORDER BY A.DNS_NAME
Are you using ODBC/OLEDB? I'm not sure if either of them support CTEs. I assume that you have tried to run the CTE from the Management Studio and it works fine. Is this right? I've always used Views to generate the data while using the ODBC/OLEDB drivers. Can you move this logic to Views/Stored Procs?
.png "Former Employee"){kind=icon}
