Column Info only Returned with FMTONLY set to OFF

Question

I have a query that is dynamically built after looking up a field list and table name. I execute this dynamic query inside a stored proc. The query is built without a where clause when the two proc parameters are zero, and built with a where clause when not.

When I execute the proc with SET FMTONLY ON exec [cpExportRecordType_ListByExportAgentID] null, null It returns no column information. I have just now replaced building the query without a where clause to just executing the same query directly, and now I get column information. I would love to know what causes this, anyone?

Answer 1

Perhaps it is related to the fact that the passed parameters are NULL, check how your query is build perhaps it behaves in different way then expected when you pass NULL. Does you proc returns expected results when you call: SET FMTONLY OFF exec [cpExportRecordType_ListByExportAgentID] null, null ?

Other possibility: I understand that you build your query dynamically by getting results from calling another queries to get the column names. Perhaps the query that would normally give you the column names returns no data but only column information (SET FMTONLY ON) so you do not have data to build you dynamic query.

Answer 2

kristof:

so you do not have data to build you dynamic query.

With null parameters my dynamic query was a pure string literal, independent of data. Changing it to a static query solved the problem.