Using SET FMTONLY OFF in the Query string of Report Server

By default FMTONLY is set to ON in Report Server. When using a SQL statement containing temp tables an error message commonly occurs. You may get an message like "There is an error in the query. Invalid object name '#myTempTable'".



To fix this I’ve used the command SET FMTONLY OFF at the top of my SQL statement. This allows the SQL statement to return rows with data. The SET FMTONLY ON will only return column information.





Use the following SQL statement, changing the FMTONLY from ON to OFF, in Query Analyzer to see how this works.

USE AdventureWorks;
GO
SET FMTONLY ON;
GO
SELECT *
FROM HumanResources.Employee;
GO
SET FMTONLY OFF;
GO