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.

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 :)

Book Review - Microsoft Dynamics AX 2012 Reporting CookBook

Dear Readers,

Wish you all a Happy 2014!

Thanks once again to Packt Publishing to send me a copy of Microsoft Dynamics AX 2012 Reporting Cookbook to review.

I had a chance to go through this book and this is a well written book easy to understand practical examples which can be used on a day to day basis for developers who are authoring SSRS Reports.

About the Author

I know Kamal through his blogs and contribution to Dynamics AX Community. Kamal has a new blog here http://dynamicsaxlounge.com/ More about the author below

Kamalakannan Elangovan

Kamal has over eight years of development experience in Dynamics AX. He shares a passion for product development and has pioneered multiple ISV solutions on Dynamics AX. In the past he worked with Innovites to create the first multidimensional ISV solution for cable industries called "Innovites for cable". Building the solution from scratch, he has gained great insights into building, selling, and promoting the product among customers and partners in the Microsoft Ecosystem. You can learn more about him at http://about.me/casperkamal. He is enthusiastic about sharing his learning with the community which led him to create one of the first few blogs for AX in 2006. It is currently available at http://kamalblogs.wordpress.com. He is active through twitter and the community, popularly by his pseudonym Casperkamal.

Audience for this book

"Microsoft Dynamics AX 2012 Reporting Cookbook" is recommended for Dynamics AX developers and .NET-based SSRS developers looking to familiarize themselves with the AX reporting framework. A basic understanding of Morphx and X++ is assumed but the step-by-step instructions make it easy even for a beginner. Some examples use C# and VB.NET, so experience with Visual Studio is a plus, but not a must.

Overview (Highlights from this book)

• Create and deploy reports
• Understand the report programming flow
• Modify the reporting framework
• Create RDP-based reports
• Design tabular matrix and chart-based reports
• Debug reports, the reporting framework, and business logic
• Migrate reports from the legacy reporting framework
• Use web services, OLAP, and external data sources to create your report
• Learn how to approach report modifications

You can find a detailed chapter coverage here http://www.packtpub.com/microsoft-dynamics-ax-2012-reporting-cookbook/book

To conclude, I would highly recommend this book for Dynamics AX 2012 SSRS Report Development

Christmas Campaign 2013 - Packt’s $5 eBook Bonanza is back!

Press release

Packt’s $5 eBook Bonanza is back!  

Following on from the success of last year’s festive offer, the publisher will be celebrating the holiday season with an even bigger $5 Bonanza.

From December 19^th, customers will be able to get any eBook or Video from Packt for just $5. This sale covers every title in the 1700+ range and customers can grab as many as they like until January 3^rd 2014 – more information is available at http://bit.ly/1jdCr2W

Tip - SSRS Time Format functions for converting to am/pm

Dear Friends,

Sometime back I was working on few standalone SSRS reports which uses time fields from AX 2009 database like for e.g. smmActivities table and the time here is stored in form of integer values.

How to convert int field into time (am/pm)

=Format(TimeSerial(0,0,Fields!starttime.Value),"hh:mm:ss tt") - 12 hour clock
=Format(TimeSerial(0,0,Fields!starttime.Value),"HH:mm:ss tt") - 24 hour clock