Microsoft Dynamics AX (Archived)

How to sync, unsynced records from Retail Store to AX database using P job?

(0) Share

Report

Posted on by Mohd saddaf khan

Hi folks,

facing a problem to sync records from Store database to AX.

Already following another thread for this

https://community.dynamics.com/ax/f/33/t/194793

Using AX 2012 R3.

As a part of cleanup procedure, my client resets channel database every 1 year(Can't say how they do that) so replication counter again starts from 1.

But AX database contains all previous records. I think, because all the replication counters are already existing in AX database(previous year records), AX is not able to pull any data from store.

I am following all the things which is mentioned in the thread,

1. Updated replication for store as 0 in Retailtransaction* tables in AX.

2. Updated FilterMax values as 0 in [crt].[TABLEREPLICATIONLOG] in channel database.

Is there anything else required from AX or channel database?

After I ran P job, FilterMax values in [crt].[TABLEREPLICATIONLOG] updated as it was before.

I can see new RPF file is created in Upload folder but no change in AX data.

Urgent help needed. Thanks!

*This post is locked for comments

I have the same question (0)

All responses (23)

Answers (1)

Rodolfo Recalde on at

Like (0)

Report

Hi Saddaf,

1. Updated replication for store as 0 in Retailtransaction* tables in AX.

I believe that this procedure (update to zero) would not be necessary.

If the problem persists, you could try increase (with a numbering above that exists in AX DB) the ReplicationCounter of the Retail related tables in Channel DB (in a testing environment)

2. Updated FilterMax values as 0 in [crt].[TABLEREPLICATIONLOG] in channel database.

Take a look in incident 3211092 Transactions are not getting pulled to store HQ in LCS:

MITIGATION

How does Async Client know what records to upload during a P job?

In the channel database the following table is checked for each table:

SELECT ISNULL(MAX(FilterMax), 0) FROM crt.TABLEREPLICATIONLOG WHERE TABLENAME='ax.RETAILTRANSACTIONSALESTRANS' AND FILTERFIELDNAME = 'REPLICATIONCOUNTERFROMORIGIN'

This check is done with all the tables that is sent to the HQ.
So one thing you could do is to force the Channel Database into thinking that the records haven’t been uploaded to Async Server. If that table was cleared out it would “reset” the channel database and it would re-upload all records into a new RPF file. This should be OK since any duplicate transactions would be ignored by AX.

Follow this steps:

1. Stop the AsyncClient in the StoreDB machine
2. Make a backup of the Channel Database
3. Delete records from the table crt.TABLEREPLICATIONLOG
4. Start the Async Client
5. Run the P-job

***

I Hope this Helps!

Regards,

Rodolfo Recalde

Was this reply helpful? Yes No
Mohd saddaf khan 20 on at

Like (0)

Report

Thanks for replying Rodolfo,

I got the success in Test system which has configured everything locally (in single machine).

Got the strange case for me when trying to apply the same for production, there is no data available in CRT.tablereplication log.

Store is located externally and is not in LAN of AX headquarter.

I checked event viewer, its showing few errors for ASYNC CLIENT as: (I think bcoz of this issue, Upload files are not getting generated. Can't identify whats the issue here?)

1.

ApplySessionFileToClientDatabase encountered ProcessSourceRequestHeaderExceptionMicrosoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessSourceRequestHeaderException: ProcessTargetRequestHeader failed due to an InvalidOperationException.

connectionString: Data Source=*****;Initial Catalog=*****ChannelDB;Integrated Security=True;Connect Timeout=60;Application Name="Commerce Data Exchange Async Client" ---> System.InvalidOperationException: The given value of type String from the data source cannot be converted to type datetime of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a DateTime. ---> System.FormatException: String was not recognized as a valid DateTime.

at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)

at System.Convert.ToDateTime(String value, IFormatProvider provider)

at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)

at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)

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

at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)

at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)

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

at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)

at System.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)

at System.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source)

at System.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)

at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)

at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)

at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)

at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)

at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)

at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)

at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)

at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.BulkCopyData(SqlConnection connection, SqlTransaction transaction)

at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.Run(SqlConnection connection, SqlTransaction transaction)

at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessWriteRequest(SqlConnection connection, SqlTransaction transaction)

at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessTargetRequestHeader(ISCTargetRequestHeader targetRequestHeader)

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

at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessTargetRequestHeader(ISCTargetRequestHeader targetRequestHeader)

at Microsoft.Dynamics.Retail.SynchClient.Core.DownloadAgent.ApplySessionFileToClientDatabase(SessionManager sessionMgr, String fileName)

----------------------------------------------

2.

Exception when converting job defination to request header. Error Details: System.NullReferenceException: Object reference not set to an instance of an object.

at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.CalculateFilterMinValue(Int64 rerunUploadSessionId, String tableName, String fieldName)

at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.ConvertJobDefinitionToRequestHeader(Int64 rerunUploadSessionId, Job activeJob, SessionManager sessionMgr)System.NullReferenceException: Object reference not set to an instance of an object.

at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.CalculateFilterMinValue(Int64 rerunUploadSessionId, String tableName, String fieldName)

at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.ConvertJobDefinitionToRequestHeader(Int64 rerunUploadSessionId, Job activeJob, SessionManager sessionMgr)

Was this reply helpful? Yes No
Rodolfo Recalde on at

Like (0)

Report

I believe that this error message:

"The given value of type String from the data source cannot be converted to type datetime of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a DateTime. ---> System.FormatException: String was not recognized as a valid DateTime."

Is related to the application of some package. (1040...1010....) Check in the download sessions form if any package is in error.

Run the SQL PROFILER to identify which table/field is related to the database problem. Possibilities:

a. Fields there is a nonconformity In the table/fields mapping | Transfer Field List;

b. Datetime format different on local machine compared to production machine. (Region settings)

About P-Job:

Verify that the file is created in the upload folder. And check the status in upload session.

I Hope this Helps.

Regards,

Rodolfo Recalde.

Was this reply helpful? Yes No
Mohd saddaf khan 20 on at

Like (0)

Report

No file is being created in upload folder.

That's the root issue for me, file is not getting created for any store. Upload session does not creates any new rows as no file is getting created.

Though, upload session reprocesses the already created files in upload folders.

No data exists in CRT.tablereplicationlog currently. and this table is not getting populated anytime. I think the 2nd error I have mentioned above has something to do with this.

Got one more thread on my issue:

https://community.dynamics.com/ax/f/33/t/181851

but no luck for me, unchecked Rerun , restarted Async server on IIS, restarted Async client service on store but without any progress...

Thanks.

Was this reply helpful? Yes No
Rodolfo Recalde on at

Like (0)

Report

"No data exists in CRT.tablereplicationlog currently"

Any new transactions at this DB? The store is operational? Share event viewer from upload job.

Was this reply helpful? Yes No
Mohd saddaf khan 20 on at

Like (0)

Report

may be , the person who was trying to resolve the issue have removed the data. I am not sure though.

Yes db has lot of new transactions, store is operational.

P-job last successfully ran on 25-Aug-16.

This date differs for different stores.

I am not sure, what do you mean by Share event viewer from upload job ?

If I run P-job in AX, it runs successfully but no data gets pulled as no new files are available under Upload folder, so I can't see any issue there.

The only error I can see is on store side if I check eventviewver,

""""""""""""""""""""""""""""""""""""""""""

Exception when converting job defination to request header. Error Details: System.NullReferenceException: Object reference not set to an instance of an object.

at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.CalculateFilterMinValue(Int64 rerunUploadSessionId, String tableName, String fieldName)

at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.ConvertJobDefinitionToRequestHeader(Int64 rerunUploadSessionId, Job activeJob, SessionManager sessionMgr)System.NullReferenceException: Object reference not set to an instance of an object.

at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.CalculateFilterMinValue(Int64 rerunUploadSessionId, String tableName, String fieldName)

at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.ConvertJobDefinitionToRequestHeader(Int64 rerunUploadSessionId, Job activeJob, SessionManager sessionMgr)

"""""""""""""""""""""""""""""""""""""""""""""

same issue has been discusses on below thread

community.dynamics.com/.../181851

Thanks.

Was this reply helpful? Yes No
Suggested answer

A.Prasanna 8,223 on at

Like (0)

Report

Hi,

First check your Real Time Service is running properly .
try to enter Real time service url in browser at the problematic store server then you should see web page with some xml view. to check further add Time Clock operation to POS and check whether clock in Clock out is working, if real time service is working there should not be any error .
apart from that check real Time service Profile. Hope you did not change realtime service account password recently or it's not set to automatic password expiry and you added Realtime Service Account to ax with proper permissions.

Hope this will help.

Amith Prasanna.

Was this reply helpful? Yes No
Mohd saddaf khan 20 on at

Like (0)

Report

Hi Amith,

Issue is there for all stores.

RTS link is working on every store.

RTS is configured using admin account. AX admin is also same account.

password settings for Admin account is set as never expired.

Everything here is working fine except for P-Job.

Was this reply helpful? Yes No
Suggested answer

A.Prasanna 8,223 on at

Like (0)

Report

Try this

https://community.dynamics.com/ax/f/33/t/147643

Thanks & Regards,

Amith Prasanna

Was this reply helpful? Yes No
Mohd saddaf khan 20 on at

Like (0)

Report

Thanks for your suggestion Amith,

This solution is already provided by Rodolfo in 1st reply. But my issue is something different, In my case, CRT.tablereplicationlog table is not populating the records.

Error that I can see on eventviewver is mentioned above.

Thanks.

Was this reply helpful? Yes No