Hi Everyone!
I hope you all are doing well. Today I am facing a problem in our Pilot environment of Epicor. BAQs that utilize the FOR XML PATH command, are now broken. A quick response from Epi techs indicates that this is a known issue, and since we don’t yet have SQL 2017 or higher we can’t use STRING_AGG either.
Has anyone else come up against this issue recently? Is there a simple process for replacing FOR XML with STRNG_AGG?
In our BAQs we create a subquery that returns a list of the values we want with a comma embedded in the string. Then we use the FOR XML PATH and some REPLACE functions (to strip the XML code) to concatenate that subquery into a single cell in the main BAQ. Kind of like this post: BAQ: Calculated Field Concatenate Rows to single field
Once we get updated to the proper SQL version I will begin testing to get STRING_AGG in there. I will post a process here if no one else has any ideas.
(What follows is my humble opinion and … legal verbiage …)
The FOR XML PATH was never valid. It’s just that the BAQ designer didn’t catch the issue and it happened to work in 10.2.600 and before. Where I’ve seen this added was on the right hand side of an “=” comparison after the field. Remember “FOR XML PATH” is not part of the WHERE clause so it doesn’t make any since being specified in the right hand side of an “=” comparison.
@rich worked really hard to make Configurator Input values (which are stored in an XML blob) available to BAQs. Does this mean that PC users can no longer access those values?
And when you get to 10.2.600+ the SQL view PcInputValue is available to use in a standard BAQ with the XML elements “normalized” so you do not have to mess with the XML parsing…
I found a resolution for this issue. You should still move to STRING_AGG as soon as you get on SQL 2017 or above.
Edit the query with the issue, go to Actions\Execution Settings. In the dialog that pops up you can add a new setting called QueryOldCompanySecurity with any value (I used “True”). Make sure to set the Persist in Query flag. Hit OK, and save the query. I had a tiny bit of trouble with this UI, just don’t go back into the Query Execution Settings UI and click OK (you can click CANCEL).
Thank you Jeff!
This works out for now. Once we have SQL 2017+ I will start testing STRING_AGG and get this old code replaced. Until then, this process seems to work great! For convenience, one of our engineers took your reply and put together a quick list of steps to get a BAQ working properly.
1. Open the BAQ
2. Open "Actions\Execution Settings"
3. Add "QueryOldCompanySecurity" set to "True"
4. Make sure to set the Persist in Query flag, then click OK
5. Save the BAQ
6. Close the BAQ (if you don't do this the BAQ will not work)
7. Re-open the BAQ (query should work now)
Note: If you open "Actions\Execution Settings" the new "QueryOldCompanySecurity" setting will not be displayed. If you click OK it removes the "QueryOldCompanySecurity" setting and the BAQ will not work, if you hit Cancel the BAQ will still work. So avoid opening the "Actions\Execution Settings" after you have the query working.
Thanks everyone for the feedback and the helpful information! Take care!
Nate
Well, I may be a bit ahead of myself here. We tested our BAQ Report after fixing the underlying BAQ. But the BAQ Report still fails with a server error (bad SQL). I can get the BAQ to run fine.
Does this BAQ setting persist even when running from a BAQ Report? If not is there a way to do that?
I’m working on this right now. I’ve got a code fix and just have to do the testing in BAQ Reports as well as Dashboards. I think we just missed the latest 10.2.700 patch though.
Once I’m done, it will take a couple days to percolate through QA, etc. I will update here when it is done and which 10.2.700 patch it will be in. Thanks everybody for your feedback and keeping me honest.
