RECID Maxed out

We have been struggling with this issue of the system sequences table is getting maxed out on the RECID for some tables. While the table that gets maxed out are random and without a pattern it mostly happens for table no 65482.

We also identified that this happens mostly when some user logs into the system. Which led to a conclusion that there is something at the user login stage that is causing the problem. We took some help from microsoft support desk and generated the trace file for the login steps.

 To generate the startup trace file follow the following steps : -

1. Login to the application server where the AOS is installed. 
2. Start the performance monitor  (start -> run -> perfmon ) and initiate the trace log
3. Go to User Defined and new 
4. Create the new trace using the AOSTRACE.XML template 
5. Log in with a client and stop it

1.  Each trace would be created and saved in a folder with an .etl extension
2. Copy the trace file created and open the same in Microsoft Dynamics AX Trace Parser. (can be found on the product CD).

When the trace file was analyzed it was found that the system was trying to run the initialization checklist on startup. This was evident from the fact that there were records in the following tables with running status.

1. Syssetuplog 
2. Syssetuppartitionlog  

These records were deleted using the following scripts : -

Delete from Syssetuplog

Where description like ‘Running’

Delete from Syssetuppartitionlog

Where description like ‘Running’

Once the above records were deleted we emptied all the compilation log and libraries and did a fresh compile of the application. 

The following table was truncated in the model database to get rid of any previous logs

• SYSXPPASSEMBLY

       The following folders were truncated in the AX server folders to delete the CIL libraries

•  C:\Program Files\Microsoft Dynamics AX\60\Server\XXX\bin\XppIL

It is assumed that that the initialized checklist got initiated when the second AOS was installed on our server. The process followed to compile and load an environment with 2 AOS servers was as follows

1. Stop All AOS
2. Start one AOS and wait until it is Running
3. Run AXBUILD on that AOS C:\Program Files\Microsoft Dynamics AX\60\Server\XXX\bin\AXBUILD.exe xppcompileall
4. When Finished with compilation, Restart this AOS
5. Log into a client and run a FULL CIL
6. When finished, stop the client, Stop the AOS
7. Start the second AOS and wait until it is up running
8. Start the First AOS
9. Log in users

We also noticed an event viewer log suggesting a missing stored procedure in AX.
Run AXUTIL SCHEMA 
Run AXUTIL OPTIMIZE to rebuild the model database


Another possible reason that was potentially considered for the max out was that maybe due to internal procedures using the SystemSequences Table it is possible the locks are being escalated and the table is getting locked out. When AX is trying to read the locked table it is possible generating a higher no to avoid a duplicate.

ALTER TABLE SYSTEMSEQUENCES

SET (LOCK_ESCALATION = DISABLE);

SSRS Report Caption

Had this strange issue when the dialog of the report was not updated for the caption. The dialog picks the value for the first time from the MenuItem, however this only happens at the time of first run and there after the details are saved in usage data.

The only means to get rid of this information is to clear the usage data. Use the below filters
RecordType = Class
ElementName = SrsReportRunController

Select the data relevant to the report to be refreshed and press CTRL + F9 to delete the records.

Permissions to Dimension Control

We had this issue in an implementation, where the payroll manager was not able to view the dimensions tab on the employment form. This tab was required to be filled in by the payroll manager to cost the right dimensions for the payroll. As the control is painted using a class and there is no MenuItem to grant permissions to, this particular case has to be resolved using explicit permissions.

Below is how the permissions were granted.

1. Open the security Role using AOT, the one which requires the permissions to this tab. 
2. In the Role node expand the permissions node and then browse to forms node
3. Right click and add the form in question. You can also drag the form in this node if it is open in a separate window. 
4. Once the form appears right click on the form and add new control. You can also drag the control called TabFinancialDimension onto the form, if the form is already open in a separate window.
5. Once the tab has been added, select the tab and look up the property sheet and update the effective access property to a relevant value. 

The above case where explicit permissions are required to be granted to a specific control on a form occurs when the control property for need permission has been set to Manual. In all other cases once a permission to the form has been granted the same permissions are cascaded to all the controls on the form.

AX 2012 EP Error to display approvals page

One fine day we encountered a strange error on the Enterprise Portal. This error was flashed when the Approvals page was being displayed.

Field Group Details does not exist.

This error was quite specific that the system expects a field group called Details in some table but we were not sure where. Finally after a lot of hit and trial we found that the issue is with the way the EP approval page display is structured. The system expects that for any table participating in the workflow there should be a details field group designed.

We started getting the above error once a new workflow was enabled for the user and the workflow involved a table (where the workflow status is maintained) which was missing a group called details. We added this group to the table and the page was up again.

RAM DISK

Recently i moved from a HDD to a SDD the results are fantastic. I now boot my windows in less than 20 seconds. This led me to investigate further on SDD drives and understand what else can i do to have a good performance with my laptop (and obsessed i am with performance).

The first thing i understood is that to have a good life for the SSD we should try and limit the no of reads and write. Once of the potentials areas where this can be limited in the temp work folder for windows and internet explorer.

The solution is to create a RAM disk and move the temp contents to this disk. Obviously this would also mean that we will have to find a solution to persist the contents of the RAM Disk which otherwise looses its contents on a restart.

I landed on an application call imdisk which is a freeware and does the job pretty well. Along with imdisk we need a utility called rawcopy to store the contents of this disk on a shutdown. The right solution would be to automate the two events, for this we would need two batch files in windows called shutdown and startup respectively. The details of these files are as follows:-

contents of the shutdown batch file
C:\RAMDISK\rawcopy.exe -mld \\.\R: "C:\RAMDISK\RDrive.img"

contents of the startup batch file
imdisk -a -t vm -f C:\ramdisk\rdrive.img -m R:

These files can be scheduled either using the windows task scheduler. Alternatively an entry can be made in the group policy (gpedit.msc) under
Local Computer Policy -> Computer Configuration -> Windows Settings -> Scripts

Double click on Startup or Shutdown applets on the right pane and add the batch files therein.

XDS MyConstruct

My construct is a pattern used to implement record level security when the business logic to implement the restriction is not straight forward.

Eg. We had a requirement at a customer where the HR wanted the records to be restricted as per the position hierarchy. So if a manager logs in he should be able to see managers reporting to him and any subordinates below these managers.

The answer to the above problem is to implement the My construct pattern for security. My Construct uses temporary tables of type TempDB which are populated using a table method called XDS().  This method is available for developers to write X++ logic to populate the temporary table. After the temporary table is populated, subsequent policy queries can use this temporary table.

In MyConstructs, we have the ability to refresh the data either PerSession or PerInvocation.

Below is the code for an overridden XDS method of a table

public RefreshFrequency xds()
{
    SPYMYSubordinate                mySubordinate;
    List                            workerList = new List(Types::Int64);
    ListEnumerator                  workerListEnumerator;
    HcmEmployment                   hcmEmployment;

    List subordinate(HcmWorkerRecId _worker,List _workerList)
    {
        HcmPositionHierarchy            positionHierarchy;
        HcmPositionWorkerAssignment     positionWorkerAssignment;

        _workerList.addEnd(_worker);
        while select positionWorkerAssignment join positionHierarchy
            where positionWorkerAssignment.Position == positionHierarchy.Position &&
                  positionHierarchy.ParentPosition == HcmWorker::getPrimaryPosition(_worker) &&
                  positionHierarchy.PositionHierarchyType == HcmPositionHierarchyType::lineHierarchyType()
            join hcmEmployment
            where hcmEmployment.Worker == positionWorkerAssignment.Worker
        {
            _workerlist = subordinate(positionWorkerAssignment.Worker,_workerlist);
        }
        return _workerlist;
    }

    workerList = subordinate(currentWorker(),workerList);
    workerListEnumerator = workerList.getEnumerator();
    while(workerListEnumerator.moveNext())
    {
        mySubordinate.Worker = workerListEnumerator.current();
        mySubordinate.insert();
    }

    return RefreshFrequency::PerSession;
}

Refresh SSRS Report

There is this common problem encountered while working with SSRS reports and AX 2012. The report changes are not reflected on the SSRS Reports servers

I found a sequence of steps which if followed ensures that the Report is updated. These steps are as follows:

1. Rebuild all on Visual Studio Project
2. Add the project to AOT
3. Right click the report on AOT and Restore
4. Deploy element from AOT 

Hope for the best :-) 

whoam i ? get the SSID

get extended who am i information in the system

wmic useraccount get name,sid

AX 2012 EP Timeout

Step 1:
Increase the timeout settings for EP in AX 2012. Looks like the solution is to increase the timeout setting for the ajax asynchronous calls, this should be done on each page where the timeout needs to be increased luckliy there is a central place as well where it can be done as shown below

1) Open the master page from \Web\Web Files\Static Files\defaultaxV4 in the AOT
2) Search for the OnLoad() method to replace in something in below.

protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
AxScriptManager scripts = AxScriptManager.GetCurrent(this.Page);

if (scripts != null)
{
scripts.AsyncPostBackTimeout = 900; // 600 seconds
}

//Call Header.DataBind to generate dynamics styles in header
Page.Header.DataBind();
}


Step 2:
Sharepoint has an service known as the request management service. This service manages all incoming requests and evalutes which is best possible machine to machines in the farm to handle the request.

The basic process to manage the request service is as follows

1. Get a reference to the SPWebApplication
2. Get a reference to the request management settings for the web application
3. Set the parameter
4. Update the setting

start sharepoint 2013 management shell. It can be found in the program files using the search option in windows

$waUrl = "http://ax-app:290"
$wa = Get-SPWebApplication $waUrl
$rmSettings = $wa | Get-SPRequestManagementSettings
$req=$wa.RequestManagementSettings
$timeout2= New-TimeSpan -minutes 5
$req.Requesttimeout=$timeout2
$req.update()



Step 3:
 Increase the timeout setting for the application pool associated with bcproxy in the IIS console.

AX 2102 No Sequences

https://community.dynamics.com/ax/b/goshoom/archive/2013/10/16/year-in-number-sequence-ax2012.aspx

AX 2012 Worker Contact Information

The address book framework in AX2012 comprises of quite a complicated schema. We might often be required to upload the contact information of the employees during an implementation as the bulk might be big enough to rule out data entry.

I had this requirement to upload the contact information of employees and ended up traversing the schema for the same here it is


select LEA.*
from HCMWorker W
inner join DirPartyTable DP
    on DP.RecID = W.Person
inner join DirPartyLocation DPL
    on DPL.Party= DP.RecID
inner join LogisticsElectronicAddress LEA
    on LEA.Location = DPL.Location
where W.PersonnelNumber = 'T00062'

Database Reindex

Below is the query to reindex all the tables in a database

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Reindexing: ' + @TableName
        DBCC DBREINDEX(@TableName,' ',90)
        FETCH NEXT FROM TableCursor INTO @TableName
    END
CLOSE TableCursor
DEALLOCATE TableCursor

sp_change_users_login

Encountered a strange issue where i was not able to drop a user and was not able to link this orphan user to an id.


Finally i realized that the SID of the user in the database was not matching with the SID of the user in the windows domain. This was realized when i used the alter user command which gave a more meaningful message as compared to sp_change_users_login

ALTER USER [SDCIT\Ali]     WITH name=[SDCIT\Ali]

the option then was to delete the existing user in the database. However before i could delete the user i had to transfer all its owned objects to dbo.

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('SDCIT\Ali')

-- now use the names you find from the above query below in place of the SchemaName below
ALTER AUTHORIZATION ON SCHEMA::[SDC\Ali] TO dbo

once the user was dropped i was able to create a new user and link it to the windows domain. 

MS SQL - Split a String

This is the first time that i used the apply operator. As the name suggests the apply is an operator hence it works at each row level rather than at a data set level and this comes truly handy to solve the problem on hand.

As defined in TechNet : The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

I wanted to update a worker information and the data provided by the customer has the employee name in a single string using space as separator.

Below is the SQL statement for same.

select TCE.EmpCode   
    , substring( Emp.[Employee Name] , 1, P1.pos ) FirstName
    , case when p2.pos = 0 then '' else substring( Emp.[Employee Name] , P1.pos + 1, P2.pos - P1.pos) end MiddleName
    , case when p2.pos = 0 then substring( Emp.[Employee Name] , P1.pos + 1, len( Emp.[Employee Name] ) - P1.pos)
        else substring( Emp.[Employee Name] , P2.pos + 1, len([Employee Name]) - P2.pos) end LastName  
from TCE
left join EMP
on Emp.[Employee Number] = TCE.EmpCode
cross apply (select (charindex(' ', EMP.[Employee Name] ) ) ) as P1(pos)
cross apply (select (charindex(' ', EMP.[Employee Name] , P1.Pos + 1) ) ) as P2(pos)

AX 2012 Time Profiles

1. Profile :A Time profile is one of the key setups that is required for the time and attendance functionality. A time profile dictates the different types of attendance transactions to expect for a day.

2. Profile Type: A profile type is used to identify how a time interval is classified as either Standard time, overtime, break, and flex time on a given day. A profile type can be configured as one of the 8 profile specification types given below :

1. Clock in : Time when the worker is expected to clock in
2. Clock out: Time when the worker is expected to clock out.
3. Standard time : Time to be considered as work hours during the calculation
4. Overtime : Time to be considered as overtime during the calculation
5. Break : Time to be considered as unpaid break during the calculation
6. Paid Break : Breaks may be paid by the legal entity (Paid break). Otherwise the break time will be deducted from the work time.
7. Flex + : indicates that the flex balance is increased if the worker is at work. Typically, the Flex+ periods are put before clock-in and after clock-out. .
8. Flex -  indicates that hours are deducted from the flex balance if the worker is not at work. Typically, the Flex- periods are put between clock-in and clock-out times.

3. Profile Groups: When there is a need to group a set to time profiles together and set rules for selection of a certain profiles based on date and time constraints then profile groups can be used. 

4. Profile Relation: The profile relation on a profile group defines the profile to be activated for the worker based on their clock in time. In the screen below we can see that from 28 June to 27 July a Ramadan profile is activated which would reduce the work timings during the holy month of Ramadan.

A more visible use of the profile being decided based on the clock in time is the use of the shifts in a company. Consider a scenario when an employee could clock into any of the 3 shift that cover the 24 hours in a day one way to apply the relevant profile would be by using profile relations to decided what time profile to be applied to the worker which in turn would decide his break and overtimes.

4. Profile Calendar: Profile calendar is used to a specific work time profile to one or more workers on a specific date regardless of the clock in time.

In the screen below july 27 has been set as half day for the entire company. This form can also be used to setup holidays.

4.1 Work Planner: In the profile calendar form above there are links available to populate entries into it while compose and copy interval are simple. Work planner is a graphical tool used to create profile calendar entries graphically.

To use the form first set a selection in the list of profiles available (marked 1 in the screen below) then click on the desired cell in the grid (marked 2 in the screen below)

The data changes from this screen would be committed to the profile calendar form however records might not be visible due to the active filters on the form so make sure you open the form from the menu Human Resouce -> Area Page -> Setup -> Time and Attendance-> Time Profiles -> Profile Calendar.

Import a Department Hierarchy

Department hierarchy is one of the key elements of implementing the AX 2012 HR solution.

In this example I have used the DIXF framework for data import. The first thing that i realized is that once a hierarchy is imported it appears as a draft version which has to be published. So don't get worried when you import a hierarchy and you don't see the changes visually on the hierarchy.

The table that stores the import from the DIXF is OMREVISIONEDIT. This can be confirmed looking at the definition of the Organization Hierarchy entity in the DIXF module.

The first issue i encountered is that my legal entity was not getting attached to the department as per the csv file created. As a result i explored the transformation class in DIXF called "DMFOMHierarchyEntityClass" and looked at the function establishing the link called "generateOmParentLink" it didn't take me time to realize that the function was expecting the dataareaid instead of the name in case the organization unit was of type "legal entity"

Once updated success was on its way.

Replace with a part from Find Sting

Editplus probably is the best ascii based text editor that i have experienced so far and the most compelling reason is the find and replace capabilities that this editor allows.

I recently had this task to create an excel sheet full or formulas and just when i started writing these formulas i started wondering if i could manage a shortcut :-)

Here are the details

1. Firstly i wrote the formula on the first row using combination of absolute and relative referencing such that it could be copied to all the rows below while still referring to the right cells.
2. I realized i made a small mistake in the formulas and editing all the formulas again was a daunting task and so the exploration began.
3. I used the Excel feature to display all the formulas in the rows instead of the values using "Ctrl + ~" character combination
4. I copied all the formulas in EditPlus
5. I landed up with a string as below (only a part is pasted the actual string was quite long)

=IF($H4>$AE$1,$V4/(($H4-$G4)/30),0)

=IF($H4>$AF$1,$W4/(($H4-$G4)/30),0)

I wanted to add a AND clause in the formula and a simple find replace would not work as a part of the replace string was variable and had to reproduced from what was being replaced (highlighted in red above). So i used the tagged expression feature of editplus to copy this into the replaced string.

A tagged expression is denoted using round brackets "( )" and can be referred in the replace string using \1. So i used the below expressions

Note: a backslash is used before the $ symbol as it is a special character and the backslash is used to tell editplus to ignore the special meaning $ symbol has to it.

Find: \$H4>(\$....)

Replace : AND($H4>\1,$G4<=\1)

and got the result

=IF(AND($H4>$AE$1,$G4<=$AE$1),$V4/(($H4-$G4)/30),0) =IF(AND($H4>$AF$1,$G4<=$AF$1),$W4/(($H4-$G4)/30),0)

Access Datasheet Change font, or font style, size, and color

An access form is different from a form created in any other environment that i have seen in the sense that the form is does not have a fixed design and the same form design could open in a datasheet (grid) or a Single Form mode while the cosmetic changes on the form are retained in the single form mode during the grid mode the appearance parameters reset to system defaults

The visual parameters like Font, Style, Size and color for a form in a datasheet mode can be changed at the run time by clicking the Home Ribbon and changing the font on the toolbar.