I’ve created a CTE to get all dates from the first day of the year to the last day of the year. When I limit the date range to less than 100 days (April 10) then it runs fine. When I set the limit to 365 days (December 31) it reports "maximum recursion 100 has been exhausted before statement completion" and stops running. I’ve read that I have to set "option (maxrecursion 0)" but I can’t find anywhere to set that option. I’ve tried reading through the application help and looking through the execution settings. If there is no option for this then I’ll just make 4 sub-queries to get the first 100, second 100, etc.
If anyone wants to use my work-around, I just made 4 CTEs, that each started at a DATEADD interval of 100 and had a criteria that was less than that date plus 100 days, other than the last CTE which was a criteria of less than 365. I then used the formula below to join them into one field.
Interesting, I didnt know f.lux manipulates your screenshots, I guess its merely just a full screen overlay… I never knew that; I always assumed it messed with the Color Profile of your monitor instead.
Your workaround is the only safe and correct solution currently.
I can offer a hack to get the query tuning option into resulting sql statement but I can’t guarantee this will work in future versions.
Add a condition to the top level subquery criteria which is always true. It should compose into this:
Note, the condition is the last statement in the query. It is always true and doesn’t affect query output but let us inject the option statement to the end of the query.
Of course you are prohibited from using malicious sql instructions here, BAQ Designer will stop you if you try to drop the database via this loophole for example.
For those who are not aware, Dmitry is the lead on BAQ Development among other activities and roles. I finally drug him up here to engage with you fine folks. Be gentle on him for a bit before harassing him too much
Is there any chance we could get Scan Count, Logical Reads, and Physical Reads added to the Query Execution Message along with the Query execution total time? It would be incredibly helpful in quickly diagnosing bad BAQ’s.
