Mar 03, 2014 by André Arnaud de Calavon Category: AX2012 R2 0 comments

Within a certain installation we had the problem that continuous number sequences did not work. The number pulled from the number sequence remained at 0. When setting the number sequence to not continuous it started numbering. Other environments were working correct so what could be the problem?
When started debugging we quickly found the method where the next number was not retrieved.

NumSeq01

Interesting is that this method did not return any value in this environment, but it did in other environments. Navigating to this definition did not work. Then I remembered that some business logic is now build into stored procedures in the SQL database. Comparing the SQL databases from the different environments learned that there were stored procedures missing. So to have the stored procedures synchronized we had to copy them over. Later I will tell you how.

The used NumberSequence class in this method is a system class which calls the stored procedure. Why does Microsoft uses stored procedures? Some are used for management. I think the getNumInternal store procedure is used for performance reasons.

Initially this was also not working. Then I found a support blog from Microsoft stating that there could be something wrong and you could copy the working stored procedure like I did. Important additional information is that you also have to check the permissions on the stored procedure. After finishing this task the continuous numbers were working again like a charm.

The link to the Microsoft support blog gives you very good information how to modify the existing stored procedure. In my case the stored procedure was missing. Please don’t ask me why this stored procedure was missing as we have not found the cause.

During installation of the database this stored procedure and some others are created. So running a task from Dynamics AX is not available.

Create new stored procedure

You have to make sure you have a valid backup of you database prior to execute the steps mentioned below.

First you have to copy the working stored procedure from a working environment. You can follow the procedure from the blog mentioned earlier.

To create the new stored procedure, follow these steps:

  1. Open SQL Server Management Studio
  2. Log in
  3. Expand Databases
  4. Select your AX2012 database (e.g. MicrosoftDynamicsAX or how it is named in your database) and expand
  5. Expand Programmability
  6. Right clock on Stored Procedures
  7. Click New stored procedure…
    The next SQL Query form will open:
    NumSeq02
  8. You can remove the Author comments and the information as selected in the print screen below.
    NumSeq03
  9. Now paste the copied content directly behind the statement CREATE PROCEDURE.
    Make sure the last statement in the Query is still GO as this was not deleted in a prior step.
    NumSeq04
  10. Now you can execute the code by clicking the Execute button.
  11. Check the execute permissions like mentioned in the support blog.

You need to restart the AOS service and AX after these steps.

 

That’s all for now. Till next time!

Microsoft Dynamics AX CommunitySubscribe to this blogger RSS Feed

Leave a Comment!

Your email address will not be published. Required fields are marked *