Testing a query in 10.2.300.11 Error BAD SQL Statement, where it is working fine in 10.0.700

ERP 10
,
1

Hello,

Still trying to solve issues during testing of our future upgrade version 10.2.300.11.

Running a query in test mode I am getting a Bad SQL statement error. and to vérify server log for details…
"System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out"

Full details of error

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. —> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
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.300.11\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 116 at Ice.Blaq.Execution.QueryExecutor.<>c__DisplayClass2_0.<ExecuteAndFillDataSet>b__0(IDbConnection dbconn) in C:\_Releases\ICE\ICE3.2.300.11\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 35 at Ice.Blaq.Execution.QueryExecutionHelper.DoJobWithObject[TObj](Func2 theJob) in C:_Releases\ICE\ICE3.2.300.11\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutionHelper.cs:line 192
ClientConnectionId:ade8f65e-c263-4df0-93d8-e254b3c2775a

I assume it is a config issue with the SQL server… Where can I change the timeout so I do not have this error ?

It is a complex query that I know takes no more than 6 seconds in our current system… but the timeout error arrives at 30 seconds !!!

Worked fine before under our current version ! 10.0.700

Any help would be appreciated…

Thanks

Pierre

2

We had some BAQs in 10.0 that worked fine that got this too when we went to 10.1. There is timeout settings you can get for the BAQs to override the default. However you want to be careful and evaluate if the BAQs could be redesigned to be more efficient. Redesign was needed in some of our BAQs but also a few that just needed the timeout tweak.

3

Well that is what I am doing right now… trying to find out why it generates this latency… (I was not the original designer …) Removing all the subqueries…then one block at a time…

Will post results…

One note: I found annoying that the only message you get is Bad Sql Statement for any error… I noticed by removing the subs, I forgot about some fields to remove. Before, it would tell you a list of what is wrong within the designer… fields could not be bound etc… But now, it forces you to go check the server Epicor logs to view such details… I find this a downgrade… ;(

Pierre

4

I feel you, I wasn’t the designer of the BAQs in my situation either.

5

I believe if you click on the analyze button, sometimes it will give you a more detailed error message

6

Are you able to run a trace with SQL profiler? That would allow you to capture the actual query that it is running, which is often not what displays in the BAQ designer. I’ve seen it inject clauses under the hood to filter out customers based on sales rep assignments, or something like that.

7

Pierre - We had something similar that worked in 10.0.700.4, but not 10.2.300.7… Have you defined your company in the BAQ Criteria?

8

Yes added Company = BAQ CurrentCompany constant.

9

Ok. Got it to work !

PartTran was part of this query and one thing to avoid is looking into this table… :wink: Anyway the info retreived was available via the jobs tables so I removed it. I did so many changes I can’t remember which one(s) resolved the issue…:thinking: But the good news are that the query was able to run…

And it ran in less than 6 seconds…

But still… I am stunned to notice such difference between the two versions !

Thanks all for your support!

Pierre