BAQ Error - Dashboard Tracker

markdamen · November 23, 2022, 1:52pm

Hi

Upgraded last Friday night to Kinetic 2022.1, and most things are great - especially the ones that people properly tested and allowed us to fix before going live… .

This one is a bit weird. It’s a dashboard, with a tracker view filtering on a date column. On the deployed dashboard, if I select a date and refresh it doesn’t produce an error - but it also doesn’t work. In the Dashboard screen itself with developer mode turned on, it shows this error:

Reviewing the server side error:

Ice.Common.EpicorServerException: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
 ---> System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Ice.Blaq.Execution.QueryExecutor.ExecuteAndFillDataSetInternal(IDbConnection dbconn, QueryInfo queryInfo, DataSet resultDataset, Action`2 perfLogger, ISqlDumper sqlDumper) in C:\_releases\ICE\ICE4.2.100.16\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 112
   at Ice.Blaq.Execution.QueryExecutor.<>c__DisplayClass3_0.<ExecuteAndFillDataSet>b__0(IDbConnection dbconn) in C:\_releases\ICE\ICE4.2.100.16\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 40
   at Ice.Blaq.Execution.QueryExecutionHelper.DoJobWithObject[TObj](Func`2 theJob) in C:\_releases\ICE\ICE4.2.100.16\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutionHelper.cs:line 203
ClientConnectionId:f454269d-19df-49f2-8d27-3d6abdb3209d
Error Number:319,State:1,Class:15
   --- End of inner exception stack trace ---

The BAQ query doesn’t even include a ‘with’ clause - ignoring the warning that what is displayed might not actually be what is executed, but it’s a pretty simple query.

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */select 
	[PartLot].[FirstRefDate] as [PartLot_FirstRefDate],
	(Ice.StringTime(PartTran.SysTime, 'HH:MM:SS')) as [Calculated_LotCreateTime],
	(case Part.MtlAnalysisCode 
     when 'CU0' then 'Culture - Serac' 
     when 'CU1' then 'Culture - Bulk Fill'
     when 'UF0' then 'UF'
     when 'FR0' then 'Fresh'
   else 'Unknown Dept'
 end) as [Calculated_Dept],
	[PartLot].[PartNum] as [PartLot_PartNum],
	[PartLot].[LotNum] as [PartLot_LotNum],
	[PartLot].[ExpirationDate] as [PartLot_ExpirationDate],
	[PartLot].[SampleID_c] as [PartLot_SampleID_c],
	[UD39].[SendToLab_c] as [UD39_SendToLab_c],
	[JobHead].[ReqDueDate] as [JobHead_ReqDueDate],
	((case when JobHead.ReqDueDate is null then PartLot.FirstRefDate else JobHead.ReqDueDate end)) as [Calculated_JobDate],
	[UD39].[ViscAtPack_c] as [UD39_ViscAtPack_c],
	[UD39].[ViscAt24Hr_c] as [UD39_ViscAt24Hr_c],
	[UD39].[Bostwick_c] as [UD39_Bostwick_c],
	[UD39].[pHResultAtPack_c] as [UD39_pHResultAtPack_c],
	[UD39].[pHResult24Hr_c] as [UD39_pHResult24Hr_c],
	[UD39].[FatAtPack_c] as [UD39_FatAtPack_c],
	[UD39].[ResultTS_c] as [UD39_ResultTS_c]
from Erp.PartLot as PartLot
left outer join Ice.UD39 as UD39 on 
	PartLot.SampleID_c = UD39.SampleID_c
	 PartLot.Company = UD39.Company
inner join Erp.Part as Part on 
	PartLot.Company = Part.Company
	and PartLot.PartNum = Part.PartNum
	and ( Part.AnalysisCode = 'F/P'  )

left outer join Erp.PartTran as PartTran on 
	PartLot.Company = PartTran.Company
	and PartLot.PartNum = PartTran.PartNum
	and PartLot.LotNum = PartTran.LotNum
	and ( PartTran.TranType = 'MFG-STK'  )

left outer join Erp.JobHead as JobHead on 
	PartTran.Company = JobHead.Company
	and PartTran.JobNum = JobHead.JobNum
where (PartLot.FirstRefDate >= dateadd (day, -30, Constants.Today)  and not PartLot.PartNum in ('COD000KA', 'NEW000KA'))
order by PartLot.FirstRefDate, PartLot.PartNum

Anybody else seen anything similar in upgrades? I’d love a pointer, because nothing obvious is jumping out at me right now. I even picked up on the “change tracking context” reference and disabled the Data Directive that does Change Log for testing, no change.

Many Thanks
Mark

aosemwengie1 · November 23, 2022, 1:58pm

Does that BAQ throw an error if you execute it directly in the BAQ screen?

markdamen · November 23, 2022, 2:10pm

So if I run the dashboard without putting a date filter, it works and brings back all data that is in scope (30 days worth).

Also running from BAQ Designer works with no error, but it’s not filtering by date there like it does on the dashboard.

aosemwengie1 · November 23, 2022, 2:16pm

So I don’t think that BAQ is the problem then - is there some custom code that’s adding in the date filter?

markdamen · November 23, 2022, 3:13pm

Nope - standard Tracker view on a deployed dashboard.

jgiese.wci · November 23, 2022, 3:16pm

Dashboards with filters convert your query into a CTE and then apply the filters (unfortunately) that is where the “with” is coming from.

markdamen · November 23, 2022, 3:20pm

Thanks for the info Josh - any pointers as to where to go in trying to fix this?

I added the trace flag to server log to see what SQL is actually being called, and it’s the same as above.

aosemwengie1 · November 23, 2022, 3:21pm

What if you just delete the date filters and re-add them?

markdamen · November 23, 2022, 3:26pm

Yep I already tried to delete the tracker then re-add it, same. @jgiese.wci seems to have more info… just waiting eagerly for his response

jgiese.wci · November 23, 2022, 3:26pm

Unfortunately that’s a pretty ambiguous error it could be anywhere for real. First thing I would say is ensure since you upgraded you have updated SQL compatibility to 150 or >. Check PDT and make sure you meet all requirements.

After that run SQL profiler to capture the actual query ran and see what it looks like.

markdamen · November 23, 2022, 3:36pm

SQL Profiler looks ok, when I strip out the params and run it with hardcoded values it works ok. I can see the fact that it’s created a CTE now:

 with [SubQuery1]  as 
(select [PartLot].[FirstRefDate] [PartLot_FirstRefDate],(Ice.StringTime([PartTran].[SysTime], 'HH:MM:SS')) [Calculated_LotCreateTime],
(case [Part].[MtlAnalysisCode] 
    when 'CU0' then  'Culture - Serac' 
    when  'CU1' then  'Culture - Bulk Fill'
    when  'UF0' then  'UF'
    when  'FR0' then  'Fresh'
  else  'Unknown Dept'
end) [Calculated_Dept],[PartLot].[PartNum] [PartLot_PartNum],[PartLot].[LotNum] [PartLot_LotNum],[PartLot].[ExpirationDate] [PartLot_ExpirationDate],
[PartLot_UD].[SampleID_c] [PartLot_SampleID_c],[UD39_UD].[SendToLab_c] [UD39_SendToLab_c],[JobHead].[ReqDueDate] [JobHead_ReqDueDate],
((case when [JobHead].[ReqDueDate] is null then [PartLot].[FirstRefDate] else [JobHead].[ReqDueDate] end)) [Calculated_JobDate],
[UD39_UD].[ViscAtPack_c] [UD39_ViscAtPack_c],[UD39_UD].[ViscAt24Hr_c] [UD39_ViscAt24Hr_c],[UD39_UD].[Bostwick_c] [UD39_Bostwick_c],
[UD39_UD].[pHResultAtPack_c] [UD39_pHResultAtPack_c],[UD39_UD].[pHResult24Hr_c] [UD39_pHResult24Hr_c],[UD39_UD].[FatAtPack_c] [UD39_FatAtPack_c],
[UD39_UD].[ResultTS_c] [UD39_ResultTS_c]
from ( [Erp].[PartLot] inner join [Erp].[PartLot_UD] on [PartLot].[SysRowID] = [PartLot_UD].[ForeignSysRowID])
left outer join ( [Ice].[UD39] inner join [Ice].[UD39_UD] on [UD39].[SysRowID] = [UD39_UD].[ForeignSysRowID]) 
on ([PartLot].[Company] = [UD39].[Company] And [PartLot_UD].[SampleID_c] = [UD39_UD].[SampleID_c])
inner join [Erp].[Part] on (([PartLot].[Company] = [Part].[Company] And [PartLot].[PartNum] = [Part].[PartNum]) and ([Part].[AnalysisCode] = 'F/P'))
left outer join [Erp].[PartTran] on (([PartLot].[Company] = [PartTran].[Company] And [PartLot].[PartNum] = [PartTran].[PartNum] 
And [PartLot].[LotNum] = [PartTran].[LotNum]) and ([PartTran].[TranType] = 'MFG-STK'))
left outer join [Erp].[JobHead] on (([PartTran].[Company] = [JobHead].[Company] And [PartTran].[JobNum] = [JobHead].[JobNum]))
where ( cast( [PartLot].[FirstRefDate] as DATE)  >= DATEADD(day, - 30, '2022-11-23') and  not ( [PartLot].[PartNum] in ('COD000KA','NEW000KA') )  ) 
and ( [PartLot].[Company] is null or [PartLot].[Company] = '' or [PartLot].[Company] = 'BVD' ) 
)
select *
from [SubQuery1]  [SubQuery1]
where (PartLot_FirstRefDate = '2022-11-22T00:00:00')
order by [PartLot_FirstRefDate] ,[PartLot_PartNum]

SQL Compat Level at 150.

Would love to run Performance and Diags, but that’s giving me an error lol!!

System.TypeLoadException: Could not load type 'Epicor.ServiceModel.StandardBindings.NetTcp' from assembly 'Epicor.Ice.Shared.Wcf.Private, Version=4.2.100.0, Culture=neutral, PublicKeyToken=37a5ccb872c00aec'.

jgiese.wci · November 23, 2022, 4:17pm

Remember you are all HTTP and rest now. Whatever connection is trying to be used nettcp won’t work anymore. Check your connection settings. Also FWIW I would go to 2019 compat if you can on SQL. What version of SQL are you running?

markdamen · November 23, 2022, 4:24pm

It’s very odd - because it’s new Windows Server 2019 box, running fresh install of Kinetic 2022.1 - so not like I’ve re-used an old server.

It was a fresh install of PDT from within the Admin Console, and the settings are populated by clicking on “Read configuration from sysconfig file”.

No idea where the net.tcp bit is coming from!

SQL Compat Level 150 is 2019, which is also the SQL Server version so hopefully that bit is all good and matched up.

jgiese.wci · November 23, 2022, 4:31pm

Check your sysconfig for tcp.net

markdamen · November 23, 2022, 4:49pm

Yep, nothing in there relating to tcp! Just https.

jgiese.wci · November 23, 2022, 6:00pm

The-power-of-christ-compels-you GIFs - Get the best GIF on GIPHY

DE2018 · April 25, 2023, 8:09am

Hi Mark,

Hope your keeping well, Did you figure out how to fix the
*"System.TypeLoadException: Could not load type ‘Epicor.ServiceModel.StandardBindings.NetTcp’ from assembly 'Epicor.Ice.Shared.Wcf.Private, " error you got while trying to run Performance and Diags.

Thanks in advance

Dominick

markdamen · April 25, 2023, 8:13am

Yes - you just need to override what it populates and choose REST (HTTPS) as the method.

DE2018 · April 25, 2023, 9:04am

Thanks Mark, Your a Star

klincecum · April 25, 2023, 1:35pm