Saturday 11 May 2013

Security issue for tables with inheritance

One of my colleague is working on a project where they require to integrate using WCF service. He came across an issue where they were not getting the Product name. The issue was that on a restart of the AOS their WCF call would not return the product name.  However after someone accesses the product from within AX, the WCF would return the name.

They asked for my assistance in getting the issue resolved. The symptoms that they described were hinting that it is a security related issue. The fact that product name was returned once someone accesses the product from within AX clearly showed that the AOS is caching the item name. So after cache was filled in the WCF call will get the name from the cache instead of getting it directly from the table. Usually AX will throw an error if the user did not have access to the table. But in this case there was no error.

They were using InventTable.itemName() method to return the product name. We looked at the security role/duties/privileges developed by them and all looked fine. We then decided to do things one step at a time to find out the exact place where things stopped working. We looked at the call stack of method itemName and started replacing the method with the code inside the method.

Eventually we found the code that was causing the issue. The code is in InventTable.productName.

   return EcoResProduct::find(productRecId).productName(_languageId);  

When we replaced this call with the code inside the EcoResProduct.productName it started working. The developer did gave access of EcoResProduct to the role/duty. So it was really confusing why this was not working. Then I realised that this is a SuperType/SubType table and EcoResProduct is not the only table that requires access. Type hierarchy browser indicated that we need access to two more tables. Once the access was granted product name was accessible even if was not cached.

The question to ask is "Why AX does not throw error in tables involving hierarchy when the user does not have access".

This posting is provided "AS IS" with no warranties. Use code at your own risk.

Friday 10 May 2013

Financial dimension sets balance update issue

Recently we noticed that the one of our Financial dimension sets balance is not being updated. When we opened the form and looked at the record, "Rebuild balances" was greyed out as shown below


Running the "Update balances" resulted in the following error.


What this error really means is that the rebuild/update process is already running. Apparently the rebuild balance process never finished on its previous run (maybe due to process being killed by someone/the AOS crash or electricity failure etc.). We could not determine why this happened. Folks at Microsoft helped us in  resetting the status and we were able to rebuild the balances. Following job was used.

 static void ResetFinDimState(Args _args)  
 {   
   DimensionHierarchy dimensionHierarchy;  
     
   update_recordSet dimensionHierarchy  
     setting FocusState = DimensionFocusBalanceInitializationState::Initialized  
     where dimensionHierarchy.FocusState == DimensionFocusBalanceInitializationState::InProcess &&  
        dimensionHierarchy.StructureType == DimensionHierarchyType::Focus &&  
        dimensionHierarchy.DeletedVersion == 0;  
 }  

This posting is provided "AS IS" with no warranties. Use code at your own risk.

Sunday 5 May 2013

SQL script to rebuild all indexes for a table

Ideally I would recommend to use Ola Hallengren's SQL Maintenance Solution but in some cases you may have to rebuild indexes on individual tables manually. Following script will allow you to do that. Just replace <TableName> with the actual table name and if you want to build the index offline (might cause locks) set @Build_Online to 0.

 DECLARE @IndexName VARCHAR (100);  
 DECLARE @TableName VARCHAR (100);  
 DECLARE @IndexId int;  
 DECLARE @Build_Online int;  
   
 SET @TableName = '<TableName>';  
 SET @Build_Online = 1;  
   
 DECLARE indexes_cursor CURSOR FOR SELECT  
      si.index_id,  
      si.name  
 FROM sys.objects so  
 INNER JOIN sys.indexes si  
      ON so.object_id = si.object_id  
 WHERE so.name = @TableName;  
   
 OPEN indexes_cursor;  
   
 FETCH NEXT FROM indexes_cursor  
 INTO @IndexId, @IndexName;  
   
 WHILE @@FETCH_STATUS = 0  
 BEGIN  
   
      PRINT 'Defragmenting ' + @TableName + ' --> ' + @IndexName  
   
      IF @Build_Online = 1  
      BEGIN  
           EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD WITH (ONLINE = ON)')  
      END  
      ELSE  
      BEGIN  
           EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD')  
      END  
        
      SELECT  
           *  
      FROM sys.dm_db_index_physical_stats  
      (  
      DB_ID('AxaptaLive'),  
      OBJECT_ID(@TableName),  
      @IndexId,  
      NULL,  
      NULL  
      );  
   
      FETCH NEXT FROM indexes_cursor  
      INTO @IndexId, @IndexName;  
   
 END  
   
 CLOSE indexes_cursor;  
 DEALLOCATE indexes_cursor;  
 GO  

This posting is provided "AS IS" with no warranties. Use code at your own risk.