TFDQuery - Query all databases on a server

[thomh 0]

Hi,

I have the following SQL which queries all DBs on a server:

 

declare @SQL nvarchar(max)

set @SQL = STUFF((SELECT '

UNION ALL

' + 
'SELECT COUNT(DISTINCT DocNo) AS DocCount,
' + quotename(name,'''') + ' AS ClientDatabase
FROM ' + quotename(name) + '.dbo.TestData
WHERE Flags & 2 = 0
AND (Flags & 4 = 0 OR Flags & 128 = 128)
AND Flags & 2048 = 0
AND Flags & 268435456 = 0'
from sys.Databases
WHERE name IN ('C003','C100','C200')
FOR XML PATH(''),
type).value('.','varchar(max)'),1,15,'')

execute(@SQL)

 

Using it in Microsoft SQL Server Management Studio in returns the correct result set but when I use the FireDAC TFDQuery it returns the following error on the Open command:

 

[FireDAC][Phys][MSSQL]-308. Cannot open / define command, which does not return result sets. Hint: use Execute / ExecSQL method for non-SELECT commands

 

Any ideas how to get this SQL statement to work in FireDAC?

 

Thanks,
Thom
 

[haentschman 92]

Hi...

Imho is the editor in the MSSQL studio a script...not a query.  How about TFDScript instead of TFDQery?

http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Comp.Script.TFDScript

 

' + quotename(name,'''') + ' also in scripts you can use parameters

[thomh 0]

Hi haentschman,

 

Thought TFDScript was for SQL commands like INSERT, UPDATE, etc. How would you write such a script and loop the result set using TFDScript?

 

// Thom

 

[thomh 0]

Mystery solved.

 

With this particular SQL statement, TFDQuery returns this error message when the result set is empty. 

 

[FireDAC][Phys][MSSQL]-308. Cannot open / define command, which does not return result sets. Hint: use Execute / ExecSQL method for non-SELECT commands

 

// Thom
 

[Dmitry Arefiev 101]

Q3 at

http://docwiki.embarcadero.com/RADStudio/Sydney/en/TFDQuery,_TFDStoredProc_and_TFDUpdateSQL_Questions