Alerts for Number sequence numbers running out

we recently faced a problem where a number sequence reached its limit, although it was a bug in configuration and number sequence should be set to range like 9 digits 999,999,999, but this query below can be used to identify the number sequence which are reaching to its limit. 
One can setup some alerts or some batch job that may be run on monthly basis and produce number sequences that are reaching consumption to let say 70%

 SELECT [NUMBERSEQUENCE]  
    ,[TXT]  
    ,[LOWEST]  
    ,[HIGHEST]  
    ,[NEXTREC]  
       ,100 * (NEXTREC - LOWEST)/(HIGHEST - LOWEST) as 'Percent Consumed'   
  FROM [dbo].[NUMBERSEQUENCETABLE]  
  where (NEXTREC - LOWEST) >= (HIGHEST - LOWEST) * 0.2  

 

SQL Query fun: Dynamics AX 2012 Main Account – Financial dimension read

Hi,

This is new blog on knowledge share, today just got chance to work with SQL queries; i was just exploring the dimension extract for one of our customer, i produce this sql query which show main account and financial dimensions associated with main account; i though it is worth to share with AX community also.

-- This script is designed to show the values in columns for better read
-- Deleting if tmp table already exists
Begin
drop
table #MainAccountDimensionTmp
End -- creating new Tmp table.
Begin CREATE TABLE #MainAccountDimensionTmp
(
MainAccount nvarchar(100),
DisplayValue nvarchar(100),
Name nvarchar(100)
) End 

-- selecting main accounts and all financial dimensions and storing into tmp table
Begin Insert into #MainAccountDimensionTmp (Name, DisplayValue, MainAccount)
select
ddv.NAME,
ddv.DISPLAYVALUE,
MAINACCOUNT.MAINACCOUNTID
from
MainAccountLegalEntity as MALE
join MAINACCOUNT on MAINACCOUNT.RECID = MALE.MAINACCOUNT
join DEFAULTDIMENSIONVIEW as ddv on MALE.DEFAULTDIMENSION = ddv.DEFAULTDIMENSION --where MAINACCOUNTid = 'test01'
end 

-- reading again from tmp table in way that values are showin in column.
-- please add as many dimesions as you want, like i have added 4 dimensins below.
Select
MainAccount,
MAX(
Case Name When 'Department' then DISPLAYVALUE ELSE '' END
) AS 'Department',
MAX(
Case Name when 'ICP' then DisplayValue ELSE '' END
) AS 'ICP',
MAX(
Case Name when 'MISC' then DisplayValue ELSE '' END
) AS 'MISC',
MAX(
Case Name when 'Expense_Class' then DisplayValue ELSE '' END
) AS 'Expense Class'
from
#MainAccountDimensionTmp Group by MainAccount


--select * from DEFAULTDIMENSIONVIEW

Query results:

 

 

AX 2012 Call Restful API using basic authentication X++ Dynamics AX 2012

Below code helps you to call the Restful API using basic authentication method of adding Authorization header using HTTP Post method.

 static void ConsumingRestService(Args _args)  
 {  
   str destinationUrl = 'Json Url here', requestXml, responseXml;  
   System.Net.HttpWebRequest    request;  
   System.Net.HttpWebResponse   response;  
   CLRObject            clrObj;  
   System.Byte[]          bytes;  
   System.Text.Encoding      utf8;  
   System.IO.Stream        requestStream, responseStream;  
   System.IO.StreamReader     streamReader;  
   System.Exception        ex;  
   System.Net.WebHeaderCollection httpHeader;  
   str               byteStr;  
   System.Byte[]          byteArray;  
   System.IO.Stream        stream;  
   System.IO.Stream        dataStream;  
   byteStr = strfmt('%1:%2', "USERNAME", "PASSWORD");  
   requestXml = " { \"storeId\": 25001, \"terminalId\":\"012\", \"transactionSequenceNumber\":\"031949640279\", \"lookup\" : { \"nameSearch\" : { \"lastName\" : \"Jones\",\"zipCode\" : \"214034702\" } } }";  
   try  
   {  
     new InteropPermission(InteropKind::ClrInterop).assert();  
     httpHeader = new System.Net.WebHeaderCollection();  
     clrObj = System.Net.WebRequest::Create(destinationUrl);  
     request = clrObj;  
     utf8 = System.Text.Encoding::get_UTF8();  
     bytes = utf8.GetBytes(requestXml);  
     request.set_KeepAlive(true);  
     request.set_ContentType("application/xml");  
     utf8    = System.Text.Encoding::get_UTF8();  
     byteArray  = utf8.GetBytes(byteStr);  
     byteStr   = System.Convert::ToBase64String(byteArray);  
     httpHeader.Add("Authorization", 'Basic ' + byteStr);  
     request.set_ContentType("text/xml; encoding='utf-8'");  
     request.set_ContentLength(bytes.get_Length());  
     request.set_Method("POST");  
     request.set_Headers(httpHeader);  
     requestStream = request.GetRequestStream();  
     requestStream.Write(bytes, 0, bytes.get_Length());  
     response = request.GetResponse();  
     responseStream = response.GetResponseStream();  
     streamReader = new System.IO.StreamReader(responseStream);  
     responseXml = streamReader.ReadToEnd();  
     info(responseXml);  
   }  
   catch (Exception::CLRError)  
   {  
     //bp deviation documented  
     ex = CLRInterop::getLastException().GetBaseException();  
     error(ex.get_Message());  
   }  
   requestStream.Close();  
   streamReader.Close();  
   responseStream.Close();  
   response.Close();  
 }  

Thanks for reading the blog
Happy daxing 🙂

Dynamics ax 2012 : AxBuild.exe xppcompileall stuck on 3 asterisks

Recently with one of the customer i faced this issue, Each time the compile starts, it just shows the AxBuild prompt with 3 asterisks and the window closes after about 15-20 minutes (without actually doing anything).

Event i have run the command as Admin but nothing worked and faced same problem

axbuild.exe  xppcompileall  /s=01 /altbin=”C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin”

After enough research i came to know that this is possible in two scenarios. The first one is the permissions, ensure the user running axbuild.exe has the same permissions as the AOS Service account and account should have enough access on AX database also. For me this was the problem the account through which i am login to system does not have enough permission to AX database.

AxBuild.exe must be run by an account that has no less security authority than the account that runs the permanent AOS has.

Source: https://msdn.microsoft.com/en-us/library/dn528954.aspx

The second time, it happened when the SQL Server had no more RAM resources available. Then fix would be to clear memory for SQL service.

Thanks for reading

Happy daxing 🙂

Amir