The PartHotList BAQ is not working in Cloud

Epicor ERP 10
,
1

The PartHotList BAQ is not working in Cloud. It works fine in production. Analyze says it’s ok, but I get a Bad SQL statement error when I run it.
PartHotList (1).baq (168.7 KB)

Ice.Common.EpicorServerException: Query Id: apf_CS0003941806. Incorrect syntax near the keyword ‘and’.
Incorrect syntax near the keyword ‘case’.
Incorrect syntax near ‘Part’.
Incorrect syntax near ‘)’.
Incorrect syntax near ‘PartPlant’.
Incorrect syntax near ‘)’.
Incorrect syntax near ‘PartPlant’.
Incorrect syntax near ‘)’.
Incorrect syntax near ‘PurAgent’.
Incorrect syntax near ‘PartWhse1’.
Incorrect syntax near ‘DMD’.
Incorrect syntax near ‘JobCustomer’.
Incorrect syntax near ‘LastChgLog’.
—> System.Exception: Query Id: apf_CS0003941806. Incorrect syntax near the keyword ‘and’.
Incorrect syntax near the keyword ‘case’.
Incorrect syntax near ‘Part’.
Incorrect syntax near ‘)’.
Incorrect syntax near ‘PartPlant’.
Incorrect syntax near ‘)’.
Incorrect syntax near ‘PartPlant’.
Incorrect syntax near ‘)’.
Incorrect syntax near ‘PurAgent’.
Incorrect syntax near ‘PartWhse1’.
Incorrect syntax near ‘DMD’.
Incorrect syntax near ‘JobCustomer’.
Incorrect syntax near ‘LastChgLog’.
—> System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword ‘and’.
Incorrect syntax near the keyword ‘case’.
Incorrect syntax near ‘Part’.
Incorrect syntax near ‘)’.
Incorrect syntax near ‘PartPlant’.
Incorrect syntax near ‘)’.
Incorrect syntax near ‘PartPlant’.
Incorrect syntax near ‘)’.
Incorrect syntax near ‘PurAgent’.
Incorrect syntax near ‘PartWhse1’.
Incorrect syntax near ‘DMD’.
Incorrect syntax near ‘JobCustomer’.
Incorrect syntax near ‘LastChgLog’.
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) 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, Action2 perfLogger, ISqlDumper sqlDumper) in C:_releases\ICE\ICE4.2.400.0FW\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 112
at Ice.Blaq.Execution.QueryExecutor.<>c__DisplayClass3_0.b__0(IDbConnection dbconn) in C:_releases\ICE\ICE4.2.400.0FW\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.400.0FW\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutionHelper.cs:line 221
ClientConnectionId:a3bff572-b850-48c5-b4b6-a876c9ab6a74
Error Number:156,State:1,Class:15
— End of inner exception stack trace —
— End of inner exception stack trace —
CorrelationId: 5d788289-cd8a-4639-bfbd-798559f53b33

Error in SQL:
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword ‘FOR’.
Msg 156, Level 15, State 1, Line 86
Incorrect syntax near the keyword ‘as’.
Msg 156, Level 15, State 1, Line 98
Incorrect syntax near the keyword ‘as’.
Msg 156, Level 15, State 1, Line 111
Incorrect syntax near the keyword ‘as’.
Msg 156, Level 15, State 1, Line 123
Incorrect syntax near the keyword ‘as’.

2

Hard for me to test since you have custom tables in it.
All I can suggest is to make a copy, then start deleting off tables until the Bad SQL statement message disappears. This will help you narrow down what area it’s in.

1 Like
3

Do you have access to event viewer that you can review what the error actually is?

1 Like
4

I’ve seen “Bad SQL statement” mean “this took a long time and it gave up.” I.e. it’s not bad SQL at all.

Try extending the timeout limit.

Also, make sure you aren’t dividing by zero.

5

99% of the time it’s data related. Divide by zero usually, but occasionally illegal character.

6

Its Kinetic in the Cloud so I don’t think we have access to event viewer

7

post your query phrase please. We can take a look.

8

Dang that’s brutal. What if you get “Something Has Gone Wrong” errors you can’t see the actual errors in the event log for those either then? How do you troubleshoot stuff like this otherwise?

9

1 Like
10

:batsignal: