Dynamics Community Forum Thread Details

I'm having a problem finishing up an on-premise setup of Microsoft Dynamics. The issue is an AosSqlDatastack Error. The DBSync package scripts seem to be creating bad SQL create view statements. The version being provisioned dis 10.0.46. I don't know if anyone else is facing this issue and how it is being solved. After every sql data stack error the AOS is marked as dead. I have rerun the update several times and the AOS machines take turns and eventually fail. dropping the views doesn't help at all. Here are the malformed queries and the errors pasted below. Any suggestions will be highly appreciated. Thank you.

An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

callStack 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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClass1.b__0() at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.b__35_0(SqlCommand c) at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func2 sqlFunc, String loggableStatement)

statement CREATE VIEW [DBO].[PURCHREQTABLEMYREQUISITIONSVIEW] AS SELECT T1.RECID AS PURCHREQRECID, T1.PARTITION AS PARTITION, T1.RECID AS RECID FROM PURCHREQTABLE T1 WHERE EXISTS (SELECT TOP 1 ' x ' FROM PURCHREQLINE T2 WHERE( 0 AND (( T1.RECID = T2.PURCHREQTABLE) AND ( T1.PARTITION = T2.PARTITION))))

errorCode 4145

sqlState 1

AXDB

errorMessage An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

statement CREATE VIEW [DBO].[SALESORDERUPDATELINESPARAMETERSENTITY] AS SELECT T1.TABLE2LINEUPDATE AS SALESORDERLINEUPDATERULE, T1.FIELDID AS FIELDID, T1.RECVERSION AS RECVERSION, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION, T1.RECID AS RECID, T2.NAME AS SALESORDERHEADERFIELDNAME, T2.RECVERSION AS RECVERSION#2, T2.RECID AS RECID#2 FROM SALESTABLE2LINEPARAMETERS T1 CROSS JOIN SQLDICTIONARY T2 WHERE(( N'35,44,45,51,53,97,105,106,109,135,146,160,206' AND ( T2.TABLEID = 22489)) AND ( T1.FIELDID = T2.FIELDID))

errorCode 4145

AXDB

errorMessage An expression of non-boolean type specified in a context where a condition is expected, near 'GROUP'.

statement CREATE VIEW [DBO].[MARKUPTRANSLINETOTALCHARGEAMOUNTVIEW] AS SELECT SUM(T1.CALCULATEDAMOUNT) AS CHARGEAMOUNT, T1.TRANSRECID AS CUSTINVOICETRANSRECID, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION, 1010 AS RECID FROM MARKUPTRANS T1 WHERE N'25583' GROUP BY T1.TRANSRECID, T1.DATAAREAID, T1.PARTITION

An expression of non-boolean type specified in a context where a condition is expected, near ')'. Incorrect syntax near ')'. Incorrect syntax near ')'.

statement CREATE VIEW [DBO].[RETAILACTIVEDISCOUNTS] AS SELECT T1.NAME AS NAME, T1.RECID AS DISCOUNTRECID, T1.OFFERID AS OFFERID, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION, T1.RECID AS RECID FROM RETAILPERIODICDISCOUNT T1 WHERE(((((( T1.VALIDFROM < '09/28/2026 00:00:00') AND ( T1.VALIDTO > '09/26/2026 00:00:00')) AND ( T1.STATUS = 1)) AND N'Simple\ discount,Discount\ with\ quantity\ limit,Least\ expensive\ favor\ retailer,Mix\ and\ match\ discount,Quantity\ discount,Threshold\ discount,Free\ item') AND EXISTS (SELECT TOP 1 ' x ' FROM RETAILDISCOUNTPRICEGROUP T2 WHERE(((( T1.OFFERID = T2.OFFERID) AND ( T1.DATAAREAID = T2.DATAAREAID)) AND ( T1.PARTITION = T2.PARTITION)) AND EXISTS (SELECT TOP 1 ' x ' FROM RETAILCHANNELPRICEGROUP T3 WHERE((( T2.PRICEDISCGROUP = T3.PRICEGROUP) AND ( T2.PARTITION = T3.PARTITION)) AND EXISTS (SELECT TOP 1 ' x ' FROM RETAILCHANNELTABLE T4 WHERE((( T3.RETAILCHANNEL = T4.RECID) AND ( T3.PARTITION = T4.PARTITION)) AND EXISTS (SELECT TOP 1 ' x ' FROM OMEXPLODEDORGANIZATIONSECURITYGRAPH T5 WHERE((( T4.OMOPERATINGUNITID = T5.ORGANIZATION) AND ( T4.PARTITION = T5.PARTITION)) AND EXISTS (SELECT TOP 1 ' x ' FROM OMHIERARCHYRELATIONSHIP T6 WHERE(( T5.ANCESTORORGANIZATION = T6.CHILDORGANIZATION) AND ( T5.PARTITION = T6.PARTITION)))))))))))) AND EXISTS (SELECT TOP 1 ' x ' FROM RETAILPERIODICDISCOUNTLINE T7 WHERE(((( T1.OFFERID = T7.OFFERID) AND ( T1.DATAAREAID = T7.DATAAREAID)) AND ( T1.PARTITION = T7.PARTITION)) AND EXISTS (SELECT TOP 1 ' x ' FROM RETAILGROUPMEMBERLINE T8 WHERE((( T7.RETAILGROUPMEMBERLINE = T8.RECID) AND ( T7.PARTITION = T8.PARTITION)) AND EXISTS (SELECT TOP 1 ' x ' FROM ECORESCATEGORY T9 WHERE((( T8.CATEGORY = T9.RECID) AND ( T8.PARTITION = T9.PARTITION)) AND EXISTS (SELECT TOP 1 ' x ' FROM ECORESCATEGORYHIERARCHY T10 WHERE(( T9.CATEGORYHIERARCHY = T10.RECID) AND ( T9.PARTITION = T10.PARTITION))))))))))

errorCode 4145