FOR XML in BAQ

Epicor ERP 10
,
1

We have a BAQ that hasn’t been run in over a year. It worked fine before but we have since upgraded both our version of Epicor (10.2.500.x => 10.2.700.34) and SQL (2016 to 2019) and now the BAQ returns “Severity: Error, Text: Bad SQL statement.” In reviewing some threads on this forum, it seems that cases where BAQs used FOR XML have stopped working at some point. I understand that I should use STRING_AGG instead now.

My problem is, I can’t find anywhere in the BAQ that FOR XML is used except for when I go to the GENERAL tab and look at the SQL query being generated:

Screen Shot 2022-10-05 at 3.57.13 PM
Screen Shot 2022-10-05 at 3.57.13 PM1828×978 158 KB

So my question is, how do I atually find that and replace it? It is not in any of the filters and not in any of the calculated fields. I don’t know where it is hiding for me to replace it!

Attached is the BAQ itself, if that helps. Someone much wiser than I will probably know how to fix this in about 30 seconds.
OpenJobs3.baq (34.7 KB)

2

It’s in the Subquery criteria for Subquery2:

image
image1058×852 39.6 KB

I saw that the FOR XML was in a Where clause inside something called AllIssued, so I looked for that calculated field and saw that the value was coming from Subquery2. I then looked at the criteria since it was in a where clause in the SQL text.

4 Likes
3

Thank you!!

4

As general rule, you should be able to identify those BAQs in the Conversion Workbench, item 130. It should have errors. If you double click it, sort by error, this will show all BAQs that didn’t upgrade well.

image
image1282×664 46.9 KB

1 Like
5

Is there a simple copy/paste replacement for the FOR XML cases? I’m having trouble understanding how it worked in the first place.

Specifically in my case, the code is:
JobOper.JobNum FOR XML PATH(‘’)

and I’m not sure how to replace that with STRING_AGG(). I tried this but it doesn’t work:
JobOper.JobNum STRING_AGG(‘’)

6

FOR XML PATH versus STRING_AGG - ERP 10 - Epicor User Help Forum (epiusers.help)

7

Thank you Mark but I read that before posting my question. It doesn’t provide an an example of FOR XML code that was in use and what it was replaced with, so I am still unsure how to do it. I also tried the workaround in that thread for Execution Settings but that doesn’t work either.

8

image
image805×361 23.6 KB

1 Like
9

Are you just getting a comma separated list or are you doing something different?

10

And that code is replacing what code? Like I said, I already read that thread. Re-posting the same thing several times over isn’t helpful. I’ve already read it several times.

My code is:
JobOper.JobNum FOR XML PATH(‘’)

I have tried both of the following and neither work:
JobOper.JobNum STRING_AGG(‘’)
STRING_AGG(JobOper.JobNum,", ")

11

Sorry, I assumed the use case was converting rows into a delimited string, which is the use case I’m most familiar with. But now I see you’re using it in the where clause of that calculated field. What did that field yield before it broke?

12

I wish I could tell you. It was written well over a year ago, maybe two years, mostly by someone else who assisted me at the time because I never really understood the whole FOR XML thing in the first place. I only understood that it did work, but not how it worked.

It is being used as filter criteria in this way:

Screen Shot 2022-10-05 at 4.35.51 PM
Screen Shot 2022-10-05 at 4.35.51 PM1912×186 31.6 KB

Are you able to interpret what that is doing from that? I sure don’t get it.

13

what SQL server version do you have?

14

2019

15

At first glance, it looks like they were using the FOR_XML to associate the JobMtl with its associated JobOper and then summing up the number of JobMtl that was issued complete.

16

so what error do you have with STRING_AGG(JobOper.JobNum,", ") ?
It should work in that version unless you have some old compatibility level

1 Like
17

The compatibility level is set to 2019.

The error is the same error that I get when trying to use the FOR XML PATH:
Severity: Error, Text: Bad SQL statement.

18

Bad sql statement is written in the UI. It does not explain anything for security reasons.
Now you have to go server event viewer or Epicor server log and find the exact error there. Without it it is just guessing.

19

While you’re checking, what is the purpose of the BAQ?

20

If I recall correctly, the BAQ returns all operations for open jobs where material is issued complete (basically a work queue) and shows how many days late that job is.

Here is the full error from the Epicor server:

Ice.Common.EpicorServerException: Invalid column name ', '. —> System.Data.SqlClient.SqlException: Invalid column name ', '.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at Ice.Blaq.Execution.QueryExecutor.ExecuteAndFillDataSetInternal(IDbConnection dbconn, QueryInfo queryInfo, DataSet resultDataset, Action2 perfLogger) in C:\_releases\ICE\ICE3.2.700.34\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 118 at Ice.Blaq.Execution.QueryExecutor.<>c__DisplayClass3_0.<ExecuteAndFillDataSet>b__0(IDbConnection dbconn) in C:\_releases\ICE\ICE3.2.700.34\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 37 at Ice.Blaq.Execution.QueryExecutionHelper.DoJobWithObject[TObj](Func2 theJob) in C:_releases\ICE\ICE3.2.700.34\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutionHelper.cs:line 195
— End of inner exception stack trace —