Sunday, February 16, 2014

Tip Dynamics AX Batch Execution and Using the NOLOCK hint to read SQL Server uncommitted data

Dear All,

Hope you are doing well.

I thought to share here a quick handy tip which I have been using for quite a while now.

Scenario:

I have a batch job running in AX which loads thousands of rows into some AX tables based on a csv/txt file. Quite often, I would like to know how many rows have been inserted into the tables.
There are many ways to get this but a quick way without much development effort is to use SQL Server Query Hint NOLOCK

Explanation:

For e.g. I'm loading some data into smmActivites table via an AX Batch and I would like to know ant any point of time how many rows have been inserted via my batch.

So, I execute the below query via SQL Management Studio

select COUNT(*) from SMMACTIVITIES WITH (NOLOCK) where DATAAREAID = 'n000'


The above statement will show me the no. of rows inserted into the table

What is the role of NOLOCK?

NOLOCK is a SQL Table hint which reads uncommitted data from database - meaning

Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data.

You can read more about NOLOCK hint from the MSDN Documentation here http://technet.microsoft.com/en-us/library/ms187373.aspx

Note - Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.

Saturday, February 15, 2014

Troubleshooting Error uploading Invoice Journals Number Sequence "0" does not exist

Dear All,

Greetings and it's been a while since I did some real posting. Had been very very busy with go live and new projects.

Recently for one of our clients, we have setup centralized payments where we raise invoice and pay them across different entities.

For one of our newly created entities, the business had an issue when do an upload of Invoice Journal.

Error: The number Sequence "0" does not exist.

Solution: I found that the issue was with the number sequence setup.









So, for e,g, in Entity B, when I go to the Number Sequence main setup form, there's a scope tab so the scope was pointing to Company A. Changed the scope and re-imported the invoice journal. Now it works like a charm!


Happy Daxing and see you around :)