I have an odd problem that’s happening. I am practicing upgrading to 10.2.700.8. We have a list of tasks that are being tested. I have one dashboard that isn’t working.
When I go in and look at the BAQ that is the source for the data, I’m getting a “Syntax is OK” when I hit Analyze, but I get the “Severity: Error, Text: Bad SQL statement” when I try to test it.
What’s even weirder is when I run it with the “Cross Company” checked, it works fine. It lists out the data from the three companies we have live at the moment. Running it from any of those companies without the “Cross Company” checked gives the error.
Our sandbox on our current live version is the same date snapshot as the upgrade test. It works fine in the sandbox.
I have no idea where to even begin…
The error log on the server shows errors around items that don’t seem to be related to the query.
Are you on SQL 2017 or newer? Try replacing the FOR XML PATH with String_Agg() and see if that fixes it. It will be better supported in the future. The XML thing is really a hack anyways.
I haven’t done a trace yet, and with plans to move to SQL Server 2019 soon there’s not really a pressing need. I believe what’s happening is when the “Cross Company” isn’t checked, it’s throwing in a filter on the Company. I think that’s affecting the FOR XML clause. I simply added the criteria to the BAQ for the current company myself and then enabled the “Cross Company” checkbox. It’s now working until I can get us to the STRING_AGG function.