This is a migrated thread and some comments may be shown as answers.

Problems with "SET FMTONLY ON"

2 Answers 31 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
RSousa
Top achievements
Rank 1
RSousa asked on 16 Nov 2017, 09:28 AM

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

2 Answers, 1 is accepted

Sort by
0
Silviya
Telerik team
answered on 21 Nov 2017, 08:13 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
RSousa
Top achievements
Rank 1
answered on 21 Nov 2017, 09:25 AM
ok... thanks.
Tags
General Discussions
Asked by
RSousa
Top achievements
Rank 1
Answers by
Silviya
Telerik team
RSousa
Top achievements
Rank 1
Share this question
or