Problems with "SET FMTONLY ON"

[RSousa]

Hi,

 

Finally discovered why some of my stored procedures are not returning the right list of columns on the Report Designer...

 

It's because the engine is sending "SET FMTONLY ON" to the SQL Server to get the list of columns... the big problem is this don't work when we use Temporary Tables inside of the store procedures (SPs).

 

I start to add SET FMTONLY OFF on the SPs that are being used on the Reports.

 

I would like to know if the use of this workaround have any other implications on the reports engine and I can continue to using it?

 

Thank You

[Silviya]

Hello RSousa,

Regarding the solution you found, note that the FMTONLY setting is deprecated. Telerik Reporting is using FMTONLY to load the columns in a dataset without dragging the data.
The newest versions of MSSQL Servers are using different workaround. You can check this help article - How to configure Stored Procedure with Temporary Tables for use with SqlDataSource component with provided solutions for versions before and after SQL Server 2016. If the using MSSQL Server is before 2016 you are right to turn FMTONLY off, but more effective solution is using memory optimized tables.

If the version of SQL Server is 2012+ you can also check this help article - SET FMTONLY OFF effects on stored procedures with dynamic query.

Hope this information helps.


Regards,
Silviya
Progress Telerik

[RSousa]

ok... thanks.