We’re identified an issue which affects later versions of MS SQL Server, where occasionally a query which you would expect to return data, returns 0 rows.
This specific issue is related to the use of SET FMTONLY in Dataset queries.
Here is what you would see in the audit tables:
In this case if you look at object # 74 , this query should always return 8 records (as it’s counting records for 8 tables). However occasionally the customer found that it was returning zero records and oddly above every hour.
If you use SQL Profiler to examine the query at this time you would see that the CPU = 0, Reads = 0, Writes = 0. You would have also seen no errors in the trace.log but that it would have said 0 records returned.
When we investigated the issue, we found that the issue had been going for some time (as recorded in the logs). This is what was happening.
So object # 70 was calling SET FMTONLY OFF; at the beginning of the query
and SET FMTONLY ON; at the end of the query
On investigation, this worked fine on MS SQL Server editions prior to 2016 but not from 2016 onwards. Nathean Analytics pools database connections for speed, performance and efficiency reasons. When a Dataset refreshes, the connection is cleaned up and left in a clean state ready for the next Dataset. However on investigation, we found that the use of FMTONLY was depreciated and unreliable. Obviously it was fine to use up until version 2016.
See these sources for more information:
The issue is resolved by removing SET FMTONLY ON; from the end of the query in the Dataset.
You can identify any Datasets in your solution which might be causing this issue using the following query:
select o.object_id, o.object_name from tbl_select s inner join tbl_object o on s.system_code = o.system_code and s.object_id = o.object_id where cast(SQL_TEXT as varchar(max)) like '%FMTONLY%'
This query will identify any Datasets which are currently set to refresh cache and are using the FMTONLY command:
SELECT O.OBJECT_NAME, OM.* FROM TBL_OBJECT_META OM INNER JOIN TBL_OBJECT O ON OM.OBJECT_ID = O.OBJECT_ID WHERE OM.OBJECT_ID IN (select OBJECT_ID from tbl_select where CAST(SQL_TEXT AS VARCHAR(MAX)) like '%FMTONLY%') AND O.CACHE_VIEW = 1
Version 2.4 onwards of Nathean Analytics has a message on saving the dataset query warning users about the use of SET FMTONLY ON in dataset queries and referencing this article.