D365 F&O DBSync Error – ##[error]SYNCENGINE(0,0): Error Number: -2,State:0,Class:11

Summary:

 

While working with the D365 F&O build server, we suddenly started to get the timeout error on the database synchronization step. Checking the logs on the azure dev ops pointed out that, db sync was not able to complete the login phase for the sql server. 

 

This was odd, as the builds have been working fine until this point. This was a wild goose chase to check the settings of the sql server, hosts file, testing pings etc …

 

Build failed:

 

This started happening after we took 10.0.8 quality update, or it was co-incidence – we are not sure yet.

 

Update 2/23/2020 10:15 PM CST:

 

• Update a development machine (not a build) with the same updates and the auto close was false – so hopefully it was an one off. This is good !!! 🙂

Resolution:

 

It came down to the “Auto close” was set to “True”, after changing that to “False”, we can see that now builds are getting done successfully.

 

Some readings/ view on auto close feature in sql server

 

• https://www.itprotoday.com/worst-practice-allowing-autoclose-sql-server-databases
• https://www.brentozar.com/blitz/auto-close-enabled/

 

The hints pointed to to look at auto close were as:

 

• DBCC Check was running again and again the AxDB, which normally shoudn’t happen.
• It was seen that AxDB start event was coming in the Application logs too frequently. 

 

Hopefully it will help other folks as well, if such error is seen in the logs.

 

You can do this by SSMS UI or using following command on a database:

 

ALTER DATABASE [AxDB] SET AUTO_CLOSE OFF;

 

 

Full error:

 

Build started 2/23/2020 4:06:12 PM.

Project “C:\DynamicsSDK\Metadata\SyncEngine.proj” on node 1 (default targets).

SyncEngine:

Database synchronization…

– Metadata binaries: K:\AosService\PackagesLocalDirectory

– Synchronization mode: fullall

K:\AosService\PackagesLocalDirectory\Bin\SyncEngine.exe -syncmode=fullall -metadatabinaries=K:\AosService\PackagesLocalDirectory -connect=”Data Source=BUILD-1;Initial Catalog=AxDB;Integrated Security=True;Enlist=True;Application Name=SyncEngine” -fallbacktonative=False -raiseDataEntityViewSyncNotification

System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was – [Pre-Login] initialization=3; handshake=5; [Login] initialization=0; authentication=2; [Post-Login] complete=14746; —> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

##[error]SYNCENGINE(0,0): Error Number: -2,State:0,Class:11

SYNCENGINE : error Number: -2,State:0,Class:11 [C:\DynamicsSDK\Metadata\SyncEngine.proj]

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

at System.Data.SqlClient.SqlConnection.Open()

at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.b__0()

at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)

at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.OpenConnection(SqlConnection conn)

at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc)

— End of stack trace from previous location where exception was thrown —

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.HandleException(ExceptionDispatchInfo edi, SqlExecutionInfo execInfo)

at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc)

at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.ConfigureSqlDatabaseSettings()

at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync()

at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options)

ClientConnectionId:c49c708e-c380-48e9-b81a-b41cca10ad10

##[error]SYNCENGINE(0,0): Error Number: -2,State:0,Class:11

SYNCENGINE : error Number: -2,State:0,Class:11 [C:\DynamicsSDK\Metadata\SyncEngine.proj]

Inner Exception: System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Database synchronization failed with exit code: -1.

Done Building Project “C:\DynamicsSDK\Metadata\SyncEngine.proj” (default targets) — FAILED.

 

Advertisement

D365 Operations – Table Synch error (Error : System.InvalidOperationException: Table ‘XXXXXXX’: Converting Field ‘XXXXXX’)

While development is done between team and multiple machines and you come across a case where you have to change the type of the column – you will get into error like below!

 

Table Sync Failed for Table:Table Sync Failed for Table(0,0): Error : System.InvalidOperationException: Table ‘XXXXXXX’: Converting Field ‘XXXXXX’ of Type ‘AxTableFieldReal’ to ‘AxTableFieldInt64’ is not support. Please drop the original field, sync the table and add new field with same name if needed.

AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException:AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException(0,0): Error : AggregateSyncException:Exception of type ‘Microsoft.Dynamics.AX.Data.Management.AggregateSyncException’ was thrown.

 

Just quickly wrote a script so that – I don’t have to do research each time.

DECLARE @COLUMNNAME AS NVARCHAR(100)

DECLARE @TABLENAME AS NVARCHAR(100)

DECLARE @CONSTRAINTNAME AS NVARCHAR(500)

DECLARE @PREVCONSTRAINTNAME AS NVARCHAR(500)

 

SET @COLUMNNAME = ‘COLUMNNAME’

SET @TABLENAME = ‘TABLENAME’

SET @CONSTRAINTNAME = ”

SET @PREVCONSTRAINTNAME = ”

 

DECLARE @SQL NVARCHAR(MAX)

 

WHILE 1=1

BEGIN

SELECT TOP 1 @CONSTRAINTNAME = dc.NAME, @SQL = N’ALTER TABLE [‘ + @TABLENAME + N’] DROP CONSTRAINT [‘ + dc.NAME + N’]’

FROM SYS.DEFAULT_CONSTRAINTS DC

JOIN SYS.COLUMNS C

ON C.DEFAULT_OBJECT_ID = DC.OBJECT_ID

WHERE

DC.PARENT_OBJECT_ID = OBJECT_ID(@TABLENAME)

AND C.NAME = @COLUMNNAME

 

IF @@ROWCOUNT = 0 OR @PREVCONSTRAINTNAME = @CONSTRAINTNAME BREAK;

 

IF LEN(@SQL) > 0

EXEC (@SQL)

 

SET @PREVCONSTRAINTNAME = @CONSTRAINTNAME;

END

 

SET @SQL = ‘ALTER TABLE ‘ + @TABLENAME + ‘ DROP COLUMN ‘ + @COLUMNNAME

IF LEN(@SQL) > 0 EXEC (@SQL)

 

SET @SQL=’DELETE FROM SQLDICTIONARY WHERE NAME=”’ + @COLUMNNAME + ”’ AND TABLEID = (SELECT TABLEID from SQLDICTIONARY where NAME=”’ + @TABLENAME + ”’ AND FIELDID=0)’

IF LEN(@SQL) > 0 EXEC (@SQL)

 

 

The script when executed will drop the column in question, it’s constraints and cleanup the SQLDictionary table for that field. This will allow the D365 Operations to do the database synchronize and create the column again.

 

CAUTION: Use this script based on your requirements and on the first place try to avoid the changing types at first place. As when you change type and follow the above approach you are going to loose information in that column.

There are other ways to take care of this like Creating a new column and moving the data over through X++ job and after that dropping the old column – but this requires multiple iterations to get done. The above script is quick way on development. Please use while understanding the RISK.

 

Enjoy.

“Finding” Dynamics 365 Operations – Trace Parser

The Azure Dev machine for D365 Operations by default doesn’t come installed with the Trace parser for it. When we try to find it it kind of hidden in the folder structure and not visible directly.

I was able to find it in following location- you can use windows search as well.

J:\DeployablePackages\[Guid]\PerfSDK\Scripts

Thanks,

Create Sales Orders through code X++ (AX2012)

Useful code below for creating sales order quickly for testing purposes in scenarios where we have to test bulk sales orders and than do some packing slips and or invoice consolidation…

static void Job_CreateSalesOrderForTesting(Args _args)
{
    SalesId salesId;
    int counter = 0;
    container saleOrdersCon;
    str salesOrders;
    int size = 100;

    void createSalesTable(CustAccount _custAccount)
    {
        SalesTable salesTable;
        NumberSeq NumberSeq;
        NumberSeq = NumberSeq::newGetNum(SalesParameters::numRefSalesId());
        NumberSeq.used();
        salesTable.SalesId = NumberSeq.num();
        salesTable.initValue();
        salesTable.CustAccount = _custAccount;
        salesTable.initFromCustTable();
        salesTable.insert();
        salesId = salesTable.SalesId;
    }

    void createSalesLine(SalesId _salesId, ItemId _itemId, str 50 _inventColorId, str 10 _inventSizeId, str 50 _configId, str 50 _inventLocationId)
    {
        InventDim inventDim;
        SalesLine salesLine;

        select firstOnly inventDim
        where
            inventDim.InventColorId == _inventColorId
            && inventDim.InventSizeId == _inventSizeId
            && inventDim.configId == _configId
            && inventDim.InventLocationId == _inventLocationId;

        if (inventDim){
            salesLine.clear();
            salesLine.SalesId = _salesId;
            salesLine.ItemId = _itemId;
            salesLine.InventDimId = inventDim.inventDimId;
            salesLine.SalesQty = 2; // You can add parameter for this as well
            salesLine.createLine
            (NoYes::Yes, // Validate
            NoYes::Yes, // initFromSalesTable
            NoYes::Yes, // initFromInventTable
            NoYes::Yes, // calcInventQty
            NoYes::Yes, // searchMarkup
            NoYes::Yes); // searchPrice
        }
    }


    for (counter = 0; counter < size; counter++){
        createSalesTable('CUS10580');
        createSalesLine(salesId, '000023000', 'RED', 'L', 'CONFIG', '009');
        createSalesLine(salesId, '000024MMI', 'BLACK', 'M', 'CONFIG', '009');
    }
}