Title: Creating Offline Database fails

Symptom:

When running the configure Offline database an error occur.

 

 

 

 

Description:

We had several cases logged were the offline database creation failed. In the following section describes a known issue with the Seed Data that leads to failure in the Offline Database creation.

The normal procedure for creating the Databases is like this.

Step 1:

 

To install the Store database

1.     Start the DB utility

2.     Fill out

       a.     Store database name

       b.    Store server name

3.     Then click on Configure offline database

4.     Fill out

       a.     Store ID

       b.    Terminal ID

       c.     Data Area ID

5.     Then click on configure store database

6.     Then click continue

  

Step 2:

Run minimum  the following jobs

N-1070

N-1090

N-1095

 

Step 3:

1.     Start DB utility again

2.     Click on the configure Offline database

3.     Fill out

        a.     Offline database name

        b.     Offline server name

4.     Click on Continue

 

 

   

Investigation:

C:\Program Files (x86)\Microsoft Dynamics AX\60\Retail Database Utility\RetailDatabaseUtility.log

In the RetailDatabaseUtility.log we can follow the activities executed by the Retail Database Utility.

In this scenario we get this error message which relates to Invalid column name 'DataAreAID' in the offline scope 'POS transactions'.

INFO   , RetailDatabaseUtility, 4, 05/14/2012 08:15:25:952, Determining meta-data for provisioning remote scope 'POS transactions'

WARNING, RetailDatabaseUtility, 4, 05/14/2012 08:15:25:967, Caught Exception: System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'DataAreAID'.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   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)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillSchemaInternal(DataSet dataset, DataTable datatable, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillSchema(DataTable dataTable, SchemaType schemaType, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillSchema(DataTable dataTable, SchemaType schemaType)

   at Microsoft.Synchronization.Data.DbSyncSchemaBuilder.FillSchema(IDbConnection connection, String tableName, IDbCommand schemaCmd, DataTable dataTable)

   at Microsoft.Synchronization.Data.DbSyncSchemaBuilder.GetSchemaFromDatabase(IDbConnection connection, Collection`1& missingTables, Exception& exp)

ERROR  , RetailDatabaseUtility, 4, 05/14/2012 08:15:25:967, Microsoft.Synchronization.Data.DbSchemaException: Cannot obtain the schema for the following tables: RETAILPOSBATCHACCOUNTTRANS. Ensure that you can establish a connection to the database and that the tables exist. Check the inner exception for any store-specific errors. ---> System.Data.SqlClient.SqlException: Invalid column name 'DataAreAID'.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   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)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillSchemaInternal(DataSet dataset, DataTable datatable, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillSchema(DataTable dataTable, SchemaType schemaType, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillSchema(DataTable dataTable, SchemaType schemaType)

   at Microsoft.Synchronization.Data.DbSyncSchemaBuilder.FillSchema(IDbConnection connection, String tableName, IDbCommand schemaCmd, DataTable dataTable)

   at Microsoft.Synchronization.Data.DbSyncSchemaBuilder.GetSchemaFromDatabase(IDbConnection connection, Collection`1& missingTables, Exception& exp)

   --- End of inner exception stack trace ---

 

 

 Issue:

In the following field lists for the POS transaction scope an “Incorrect” DataAreaID column was added.

-       Retail/Setup/POS/Profiles/Offline Profile

-       In line details choose POS Transactions

-       Click on Offline scope

-       In line details mark the first table

-       Click on Field list

-       Scroll down to the bottom

 

Cause:

When running the Seed Data generation the job inserts “DataAreAID” to the field list on non global tables, but as the tables actually has DATAAREAID column defined, it inserts it twice.

Retail/Setup/Parameters/Retail parameters/initialize

 

\Classes\RetailOfflineDefaultProfileCreator\createScopeFromJob

The line with the #DataAreaID is causing this

if (dictTable.dataPrCompany()) // Only add DataAreaId if table is not global

{

offlineColumn.clear();

offlineColumn.SyncColumnName = #DataAreaId;

offlineColumn.SyncTable = offlineTable.RecId;

offlineColumn.insert();

}

 

 

 

 

Workaround:

Delete the Column DataAreAID on all Offline tables in the POS transaction (Don´t delete DATAAREAID)

 

 

Author: Kim Truelsen

Date: 14/5-2012