FOR XML PATH versus STRING_AGG

Are you using “FOR XML PATH” in your BAQ? Just reading a column that happens to hold a chunk of XML isn’t a problem.

Yes and Thank you!

Sorry, I can’t parse that. The “Thank you” might be throwing me off.

So “Yes”, you are using “FOR XML PATH”?

Yes for “using in a BAQ” and “Thank you” because you constantly kick :peach:

1 Like

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…

4 Likes

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).

I would love to hear if this solves your problem.

4 Likes

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

5 Likes

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?

Setting should persist. Try to find out how exactly SQL looks like in report.

How do you propose I find out how the SQL looks in the report?

Probably look into SSSR log, or in SQL Profiler

I am not sure I have access to those, as we are on a cloud installation.

oh, sorry, i missed that…

When I got SSRS errors, I would submit a ticket with the approximate date/time of the error.

Vote for improved monitoring/logging.

1 Like

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. :slight_smile:

3 Likes

I just verified my fix is in 10.2.700.4. We tested that it works with Dashboards and BAQ Reports. If you see this issue anywhere else, let us know. :slight_smile:

1 Like

For the Cloud customers, the corrected elements will be installed as part of the 10.2.700.2 Upgrade.

1 Like

Can anyone tell me how to use STRING_AGG once I do have SQL2017+? As noted above, I am utilizing a subquery to concatenate the work centers for a job into a single line. I use the FOR XML PATH and REPLACE. How can I perform a similar function using STRING_AGG? Would I just add a calculated field with this formula?

STRING_AGG(JobOper.OpSeq,", ")

Thanks!
Nate

1 Like

That’s correct. Then you do everything else the same, except you don’t need to mess with the replace crap anymore.

2 Likes

Wow, that is easier than the old method! In my case I needed distinct so still used a SubQ to pull just distinct OrderNum values.

select 
	[InvcHead].[Company] as [InvcHead_Company],
	[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
	(String_Agg(OrderNums.InvcDtl1_OrderNum,', ')) as [Calculated_SONum]
from Erp.InvcHead as InvcHead
left outer join  (select distinct
	[InvcDtl1].[Company] as [InvcDtl1_Company],
	[InvcDtl1].[InvoiceNum] as [InvcDtl1_InvoiceNum],
	[InvcDtl1].[OrderNum] as [InvcDtl1_OrderNum]
from Erp.InvcDtl as InvcDtl1)  as OrderNums on 
	InvcHead.Company = OrderNums.InvcDtl1_Company
	and InvcHead.InvoiceNum = OrderNums.InvcDtl1_InvoiceNum
group by [InvcHead].[Company],
	[InvcHead].[InvoiceNum]