Reportitems not showing data with saved as PDF

don't set the group property to hide. set individual text (Field) to hide

Date format SSRS

Microsoft Dynamics AX - [Fix] Improve DocuRef Delete Performance

The problem occurred while deleting a Sales Header. But in reality we have the problem every time we remove DocuRef containing a lot of records.

https://community.dynamics.com/ax/b/dynamicsaxexperience/archive/2016/01/23/slow-docuref-delete-performance

How to get sales/purchase tax/GST/VAT info for report?

For posted Sales Invoice report

1 2	select taxTrans where taxTrans.InventTransId == _custInvoiceTrans.InventTransId;     salesInvoiceTmp.TaxValue = taxTrans.TaxValue;

For posted Free Text Invoice report

1 2 3 4

select taxTrans     where taxTrans.Source == TaxModuleType::FreeTxtInvoice         && taxTrans.SourceTableId == tableNum(CustInvoiceTrans)         && taxTrans.SourceRecId == _custInvoiceTrans.RecId;

Execute SQL in x++

server static void main(Args _args)
{
   Connection      connection;
    Statement       statement;
    str             query;
    Resultset       resultSet;
    Str1260         test = "delay";
    ;

    // create connection object
    connection = new Connection();

    // create statement
    statement = connection.createStatement();

    // Set the SQL statement
    query = "select recid from LANGUAGETXT where LANGUAGETXT.txt = '" + test +"'";

    // assert SQL statement execute permission
    new SqlStatementExecutePermission(query).assert();

    // when the query returns result,
    // loop all results for processing
    //BP Deviation documented
    resultSet = statement.executeQuery(query);

    while(resultSet.next())
    {
        // do something with the result
        info(strFmt("%1",resultSet.getInt64(1)));
    }

    // limit the scope of the assert call
    CodeAccessPermission::revertAssert();
}

Clear Master and release variant which has no transaction and barcode

Clear Release variant first and them master variant.

static void ClearReleaseVarient(Args _args)

{

    EcoResDistinctProductVariant                EcoResDistinctProductVariant, variant;

    EcoResProductVariantDimensionValue          EcoResProductVariantDimensionValue;

    EcoResProductTranslation                    ecoResProductTranslation_Variant;

    EcoResProductVariantColor                   ecoResProductVariantColor;

    EcoResProductVariantStyle                   ecoResProductVariantStyle;

    EcoResProductVariantSize                    ecoResProductVariantSize;

    EcoResProductVariantConfiguration           ecoResProductVariantConfiguration;

   

    InventDimCombination                        InventDimCombination,InventDimCombination1,InventDimCombination2;

    InventItemBarcode                           inventBarcode;

    InventTrans                                 InventTrans;

    InventDimCombination                        InventDimCombinationCheckother;

    DataArea                                    dataArea;

    boolean                                     checkdelete;

   /* EcoResSize                                  size;

    EcoResColor                                 color;

    EcoResStyle                                 style;

    EcoResProductMaster                         productmaster;

    , dimFromCombination;

    EcoResConfiguration                         config;

    InventTable                                 inventTable;

    ,ecoResProducttest;

    container                                   con;

    int                                         conin;*/

    InventDim                                   inventDim,inventDimcheck;

    EcoResProduct                               ecoResProduct;

    FromTime    startTime;

    int seconds = 1,counts;

    CommaIo file;

    container line;

    #define.ExampleFile(FileNameSave)

    FileIoPermission perm;

    CompanyInfo CompanyInfo;

    #File

    str   elapsed;

    ;

    startTime = timeNow();

 while select crossCompany InventDimCombination order by ItemId asc

                            where InventDimCombination.ItemId =="A01112Y07521"

                    notexists join inventBarcode

                           where inventBarcode.RetailVariantId == InventDimCombination.RetailVariantId

                    notexists join InventTrans

                            where InventTrans.ItemId == InventDimCombination.ItemId

                                && InventTrans.inventDimId == InventDimCombination.InventDimId

            {

                counts++;

                select ecoResProduct

                        where ecoResProduct.DisplayProductNumber == InventDimCombination.ItemId

                            && (ecoResProduct.Division == ProductTypeSetup::findByDivision("20").RecId

                            || ecoResProduct.Division == ProductTypeSetup::findByDivision("29").RecId)

                            && EcoResProduct.ProductCreationType == ProductCreationType::GlobalProduct;

                if(ecoResProduct)

                {

                    if(InventDimCombination.validateDelete())

                    {

                         select forUpdate * from InventDimCombination1 where InventDimCombination1.RecId == InventDimCombination.RecId;

                        InventDimCombination1.delete();

                    }

                }

                else

                    {

                        inventDim       = inventDim::find(InventDimCombination.InventDimId);

                        info(strFmt("Item %1, Size %2, Color %3, Config %4, Season %5 has transactions", InventDimCombination.ItemId, inventDim.InventSizeId, inventDim.InventColorId, inventDim.configId, inventDim.InventStyleId));

                    }

    }

        elapsed = timeConsumed(startTime, timeNow());

         info(strFmt("Time elapsed: %1 and number of records deleted %2", elapsed,counts));

}

-------------------------------------------------------------------------------------------------------------------

static void ClearProductMastervarient(Args _args)

{

     EcoResDistinctProductVariant                EcoResDistinctProductVariant, variant;

    EcoResProductVariantDimensionValue          EcoResProductVariantDimensionValue;

    EcoResProductTranslation                    ecoResProductTranslation_Variant;

    EcoResProductVariantColor                   ecoResProductVariantColor;

    EcoResProductVariantStyle                   ecoResProductVariantStyle;

    EcoResProductVariantSize                    ecoResProductVariantSize;

    EcoResProductVariantConfiguration           ecoResProductVariantConfiguration;

     DimensionAttributeValues                 DimensionAttributeValues;

    InventDimCombination                        InventDimCombination,InventDimCombination1,InventDimCombination2;

    InventItemBarcode                           inventBarcode;

    InventTrans                                 InventTrans;

    InventDimCombination                        InventDimCombinationCheckother;

    DataArea                                    dataArea;

    boolean                                     checkdelete;

   /* EcoResSize                                  size;

    EcoResColor                                 color;

    EcoResStyle                                 style;

    EcoResProductMaster                         productmaster;

    , dimFromCombination;

    EcoResConfiguration                         config;

    InventTable                                 inventTable;

    ,ecoResProducttest;

    container                                   con;

    int                                         conin;*/

    InventDim                                   inventDim,inventDimcheck;

    EcoResProduct                               ecoResProduct;

    FromTime    startTime;

    int seconds = 1,counts;

    CommaIo file;

    container line;

    #define.ExampleFile(FileNameSave)

    FileIoPermission perm;

    CompanyInfo CompanyInfo;

    #File

    str   elapsed;

    ;

    startTime = timeNow();

      while   select  ecoResProduct

                        where

                         ecoResProduct.DisplayProductNumber == "A01112Y07521"

                            && (ecoResProduct.Division == ProductTypeSetup::findByDivision("20").RecId

                            || ecoResProduct.Division == ProductTypeSetup::findByDivision("29").RecId)

                            && EcoResProduct.ProductCreationType == ProductCreationType::GlobalProduct

    {

     while select EcoResDistinctProductVariant order by RecId asc

     where EcoResDistinctProductVariant.DisplayProductNumber like ecoResProduct.DisplayProductNumber +"*"

        {

        select crossCompany InventDimCombination

        where InventDimCombination.DistinctProductVariant == EcoResDistinctProductVariant.RecId

        && InventDimCombination.ItemId == ecoResProduct.DisplayProductNumber;

                if(!InventDimCombination)

                {

                    ttsBegin;

                   // select forUpdate * from ecoResProductTranslation_Variant where ecoResProductTranslation_Variant.Product ==ecoResProduct.RecId;

                   // ecoResProductTranslation_Variant.delete();

                        select forUpdate * from EcoResProductVariantColor where EcoResProductVariantColor.DistinctProductVariant == EcoResDistinctProductVariant.RecId;

                    EcoResProductVariantColor.delete();

                        select forUpdate * from EcoResProductVariantStyle where EcoResProductVariantStyle.DistinctProductVariant == EcoResDistinctProductVariant.RecId;

                    EcoResProductVariantStyle.delete();

                        select forUpdate * from EcoResProductVariantSize where EcoResProductVariantSize.DistinctProductVariant == EcoResDistinctProductVariant.RecId;

                    EcoResProductVariantSize.delete();

                        select forUpdate * from EcoResProductVariantConfiguration where EcoResProductVariantConfiguration.DistinctProductVariant == EcoResDistinctProductVariant.RecId;

                    EcoResProductVariantConfiguration.delete();

                        select forUpdate * from DimensionAttributeValues where DimensionAttributeValues.EcoResDistinctProductVariant == EcoResDistinctProductVariant.RecId;

                     DimensionAttributeValues.delete();

                    InventDimCombination1=null;

                    select forUpdate * from variant where variant.RecId == EcoResDistinctProductVariant.RecId;

                    variant.delete();

                    ttsCommit;

                }

        }

    }

     elapsed = timeConsumed(startTime, timeNow());

         info(strFmt("Time elapsed: %1 and number of records deleted %2", elapsed,counts));

}

Update All table id and Field Id on SQL dict based on AOT property

Dictionary      dictionary = new Dictionary();
    DictTable    dictTable;
    DictField       dictField;
    TableId         tableId,tableIdbuffer,NewtableId;
    FieldId         fieldId,fieldIdbuffer,newfieldId,fieldIdmin,fieldIdmax;
    SqlDictionary   sqlDictionaryTable;
    SqlDictionary   sqlDictionaryField;
    SqlDictionary   SqlDictionaryupdate,SqlDictionaryupdaterecordset;
    PS_Table        PS_Table;
    int             i;

    setPrefix("Update of data dictionary IDs");
    tableId = dictionary.tableNext(0);
    ttsbegin;
    select maxof(tabid) from SqlDictionaryupdate;
    tableIdbuffer = SqlDictionaryupdate.tabId + 1000;

    ttsBegin;
    delete_from PS_Table;
    ttsCommit;

    while (tableId > 0)
    {

        dictTable = new SysDictTable(tableId);
        setPrefix(dictTable.name());

        if (!dictTable.isSystemTable())
        {
            //Finds table in SqlDictionary by name in AOT, if ID was changed.
            //Empty field ID represents a table.
            select sqlDictionaryTable
                where sqlDictionaryTable.name == dictTable.name()
                && sqlDictionaryTable.fieldId == 0
                && sqlDictionaryTable.tabId != dictTable.id();

            if (sqlDictionaryTable)
            {
                //Updates table ID in SqlDictionary
                NewtableId = dictTable.id();
                select * from SqlDictionaryupdate
                where SqlDictionaryupdate.tabId == NewtableId;
                if(SqlDictionaryupdate)
                {

                    update_recordset SqlDictionaryupdaterecordset
                    setting TabId = tableIdbuffer
                    where SqlDictionaryupdaterecordset.TabId == SqlDictionaryupdate.tabId;
                    tableIdbuffer--;

                }

                if (ReleaseUpdateDB::changeTableId(
                    sqlDictionaryTable.tabId,
                    dictTable.id(),
                    dictTable.name()))
                {
                    info(strFmt("Table ID changed (%1 -> %2)", sqlDictionaryTable.tabId, dictTable.id()));
                }
            }

            fieldId = dictTable.fieldNext(0);
            select maxof(fieldId) from SqlDictionaryupdate;
            fieldIdbuffer = SqlDictionaryupdate.fieldId + 1000;
            select minOf(fieldId) from SqlDictionaryupdate;
            fieldIdmin = SqlDictionaryupdate.fieldId;
            //For all fields in table
            while (fieldId)
            {

                info(strFmt("%1",fieldId));
                dictField = dictTable.fieldObject(fieldId);
                //    info(dictField.name());
                if (dictField.isSql() && !dictField.isSystem())
                {
                    //Finds fields in SqlDictionary by name and compares IDs



                    select sqlDictionaryField
                        where sqlDictionaryField.tabId == dictTable.id()
                        && sqlDictionaryField.name == dictField.name()
                        && sqlDictionaryField.fieldId != 0
                        && sqlDictionaryField.fieldId != dictField.id();

                    if (sqlDictionaryField)
                    {
                        Newfieldid = dictField.id();
                        NewtableId = dictTable.id();

                        ttsBegin;
                        PS_Table.TabId = NewtableId;
                        PS_Table.Tablename = dictTable.name();
                        PS_Table.FieldName = dictField.name();
                        PS_Table.FieldId = Newfieldid;
                        PS_Table.insert();
                        ttsCommit;

                        select * from SqlDictionaryupdate
                        where SqlDictionaryupdate.tabId == NewtableId
                        && SqlDictionaryupdate.fieldId == Newfieldid;
                        if(SqlDictionaryupdate)
                        {

                             update_recordset SqlDictionaryupdaterecordset
                                setting FieldId = fieldIdbuffer
                                where SqlDictionaryupdaterecordset.TabId    == NewtableId
                                   && SqlDictionaryupdaterecordset.FieldId  == Newfieldid;
                            fieldIdbuffer--;

                        }

                        //Updates field ID in SqlDictionary
                        if (ReleaseUpdateDB::changeFieldId(
                            dictTable.id(),
                            sqlDictionaryField.fieldId,
                            dictField.id(),
                            dictTable.name(),
                            dictField.name()))
                        {
                            info(strFmt("Field %1 - ID changed (%2 -> %3)",
                            dictField.name(),
                            sqlDictionaryField.fieldId,
                            dictField.id()));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }
        }
        tableId = dictionary.tableNext(tableId);
    }
    ttscommit;

Stock on hand from SQL as of date

select INVENTSUM.itemid, sum(postedqty) as Postedqty,
sum(Received) as Received,
sum(Deducted) as Deducted,
sum(Registered) as Registered,
sum(PICKED) as PICKED,
sum(RESERVPHYSICAL) as RESERVPHYSICAL,
sum(ORDERED) as ORDERED,
sum(ARRIVED) as ARRIVED,
sum(RESERVORDERED) as RESERVORDERED,
sum(ONORDER) as ONORDER,
INVENTDIM.INVENTLOCATIONID,
INVENTDIM.INVENTSITEID,
INVENTDIM.CONFIGID
from INVENTSUM
join INVENTDIM
on INVENTDIM.INVENTDIMID = INVENTSUM.INVENTDIMID
where LASTUPDDATEPHYSICAL <= '2016-04-01'
--and itemid = '200935'
group by INVENTSUM.itemid, INVENTDIM.INVENTLOCATIONID,INVENTDIM.INVENTSITEID,INVENTDIM.CONFIGID

--------------------------------------------------------------------------------------------------------
select * from (select INVENTSUM.itemid, sum(AvailPhysical) as AvailPhysical,
INVENTDIM.INVENTLOCATIONID,
INVENTDIM.INVENTSITEID,
INVENTDIM.CONFIGID,
INVENTDIM.INVENTSTYLEID,INVENTDIM.INVENTCOLORID
,INVENTDIM.INVENTBATCHID,INVENTDIM.INVENTSIZEID
from INVENTSUM as INVENTSUM
join INVENTDIM
on INVENTDIM.INVENTDIMID = INVENTSUM.INVENTDIMID
where /*LASTUPDDATEPHYSICAL <= '2016-04-01'
and */ INVENTSUM.itemid = 'A92991Y61553' and  INVENTSUM.DATAAREAID = 'USMF'
and INVENTDIM.INVENTSTYLEID != ''
group by INVENTSUM.itemid, INVENTDIM.INVENTLOCATIONID,INVENTDIM.INVENTSITEID,INVENTDIM.CONFIGID,INVENTDIM.INVENTSTYLEID,INVENTDIM.INVENTCOLORID,INVENTDIM.INVENTBATCHID,INVENTDIM.INVENTSIZEID)
 Stock
where stock.AvailPhysical >0 

Main Account Dimension update

static void MainAccountDimension(Args _args)
{
LSVVendInvStageTmp LSVVendInvStageTmp;
    MainAccountLegalEntity  MainAccountLegalEntity;
    MainAccount             mainaccount;
    CompanyInfo                         companyInfoList;
    Ledger                              ledgerList;
    MainAccountLegalEntity              mainAccountLegalEntityList;
    ;
    while select *from mainaccount
    {
   
         while select DataArea from companyInfoList
            join RecId from ledgerList
                where ledgerList.PrimaryForLegalEntity == companyInfoList.RecId &&
                    ledgerList.ChartOfAccounts == mainaccount.LedgerChartOfAccounts
            notexists join RecId from mainAccountLegalEntityList
                where mainAccountLegalEntityList.LegalEntity == companyInfoList.RecId &&
                    mainAccountLegalEntityList.MainAccount == mainaccount.RecId
        {
            MainAccountLegalEntity.clear();
            MainAccountLegalEntity.MainAccount          = mainaccount.RecId;
            MainAccountLegalEntity.LegalEntity          = companyInfoList.RecId;
            MainAccountLegalEntity.DefaultDimension     = 5637144576;
            MainAccountLegalEntity.FixedDimensions      = 0;

            MainAccountLegalEntity.insert();
        }
    }
}

Connect two DB

select sl4.salesid,sl4.itemid,sl4.LINENUM,sl4.CREATEDDATE from I5Production.dbo.SALESLINE as SL4
where NOT EXISTS

( select SL12.salesid,SL12.ITEMID,SL12.LINENUM From AxDev.dbo.SALESLINE SL12
where  sl4.salesid = SL12.salesid
and sl4.itemid = SL12.ITEMID
and sl4.LINENUM = SL12.LINENUM
and sl4.DATAAREAID = SL12.DATAAREAID
and  sl4.DATAAREAID = 'i5d'
--and  sl4.CREATEDDATE > '2015-12-31'
--and  sl4.SALESID ='S10099614'
);

Get SID

C:\Windows\system32>wmic useraccount get name,sid

Upgrade windows os from standard edition to server data center

DISM /online /Set-Edition:ServerDatacenter /AcceptEula /productkey:HXH7V

get default dimension with Attribute name and Value

 container conAttr = ["ICO"];
    DimensionAttribute                  dimensionAttribute;
    DimensionAttributeValue             dimensionAttributeValue;
    DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();
    DimensionDefault                    result;
    str dimValue = "APOL";
   
    dimensionAttribute = dimensionAttribute::findByName("ICO");
    dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,true);
    valueSetStorage.addItem(dimensionAttributeValue);
    result = valueSetStorage.save();
    print result;
    pause;

Get Voucher balance in AX 2012

ledgerJournalTrans ledgerJournalTrans;
    ledgerJournalEngine ledgerJournalEngine = new ledgerJournalEngine();
    ledgerJournalTable ledgerJournalTable;
    tmpVoucherSum       tmpVoucherSum;
    ;

    ledgerJournalTrans = ledgerJournalTrans::findRecId(5638439934,false);
    ledgerJournalTable = ledgerJournalTable::find(ledgerJournalTrans.JournalNum);
    ledgerJournalEngine.newJournalActive(ledgerJournalTable,true);
    print ledgerJournalEngine.voucherDebit(ledgerJournalTrans);




    tmpVoucherSum   = LedgerJournalEngine_Server::createVoucherBalance(ledgerJournalTrans.Voucher,
                                                                        ledgerJournalTable.JournalNum);
    print   tmpVoucherSum.debit;
    print   tmpVoucherSum.Credit;
    pause;

Hot fix installation error

An error occurred in writing to the event log on machine DBSERVER: The source was not found, but some or all event logs could not be searched.  Inaccessible logs: Security.
Error updating model database: Microsoft.Dynamics.Setup.AxSetupException: AxUtil call returned errors:The model contains a customization of a configuration key. The model cannot be imported because you can overlayer a configuration key from the patching layer only.

Solution:

Uninstall DMF for CU6 from Control panel / axutil

axutil delete /model:"Data Import Export Framework" from CMD .

Reinstall CU8

If you Need a Filter In a Form

//Add this to Class Declaration of a Form
public class FormRun extends ObjectRun
{
       QueryBuildRange rangeTenant;
       QueryBuildRange rangeAgreementId;
       QueryBuildRange rangeAgreementJournalId;
       QueryBuildRange rangeBuildingId;
       QueryBuildRange rangeRentTransId;
       QueryBuildRange rangePaymentDate;
       QueryBuildRange qbr,qbr1,qbr2,qbr3;

}
//Add this to Datasource Execute Query(Eg :- NetPaymentHistory_ds)
public void executeQuery()
{

      rangeTenant.value(TenantId.text() ? queryValue(TenantId.text()) 
              :SysQuery::valueUnlimited());
      rangeAgreementId.value(AgreementId.text() ? queryValue(AgreementId.text())
              : SysQuery::valueUnlimited());
      rangeAgreementJournalId.value(AgreementJournalId.text() ? queryValue(AgreementJournalId.text()) 
             : SysQuery::valueUnlimited());
      rangeBuildingId.value(BuildingId.text() ? queryValue(BuildingId.text())
            : SysQuery::valueUnlimited());
      rangeRentTransId.value(RentTransId.text() ? queryValue(RentTransId.text()) 
            : SysQuery::valueUnlimited());
      rangePaymentDate.value(PaymentDate.valueStr() ? queryValue(PaymentDate.valueStr())
            : SysQuery::valueUnlimited());

       super();
}
//Add this to Datasource init method (Eg :- NetPaymentHistory_ds)

public void init()
{

       super();

      this.query().dataSourceTable(tablenum(NetPaymentHistory)).clearDynalinks();

      rangeTenant = this.query().dataSourceTable(tablenum(NetPaymentHistory))
               .addRange(fieldnum(NetPaymentHistory, TenantId));
      rangeAgreementId = this.query().dataSourceTable(tablenum(NetPaymentHistory))
               .addRange(fieldnum(NetPaymentHistory, AgreementId));
      rangeAgreementJournalId = this.query().dataSourceTable(tablenum(NetPaymentHistory))
              .addRange(fieldnum(NetPaymentHistory, AgreementJournalID));
      rangeBuildingId = this.query().dataSourceTable(tablenum(NetPaymentHistory))
              .addRange(fieldnum(NetPaymentHistory, BuildingId));
      rangeRentTransId = this.query().dataSourceTable(tablenum(NetPaymentHistory))
             .addRange(fieldnum(NetPaymentHistory, RentTransId));
      rangePaymentDate = this.query().dataSourceTable(tablenum(NetPaymentHistory))
            .addRange(fieldnum(NetPaymentHistory, PaymentDate));
     // Enable caching of the document handling and note it display fields
    //purchRFQTable_ds.cacheAddMethod(tablemethodstr(PurchRFQTable, showDocHandIcon));

}
// Add this to Each of the String Control Field
public boolean modified()
{
     boolean ret;

    ret = super();

    NetPaymentHistory_ds.executeQuery();

    return ret;
}

Parent Form Refresh using x++

//Add this Method in Parent Form

void Form_doRefresh()
{
;

NetBuildings_ds.reread(); // Parent Form and Child Form Datasource Name
NetBuildings_ds.executeQuery();
NetBuildings_ds.refresh();

NetUnitDetails_ds.reread();
NetUnitDetails_ds.executeQuery();
NetUnitDetails_ds.refresh();
}

//Add this Method in Child Form where you need to Refresh the Parent Form

public void close()

{

    if(formHasMethod(element.args().caller(), identifierStr(Form_doRefresh)))//Form_doRefresh is Parent Method                                                                                                          Name
   {
        element.args().caller().Form_doRefresh();
   }

   super();
}
.................................................................................................................................................................

void bookrefresh()
{
   

.reread();

   

.refresh();

}

In the child form, you can do this –

void bookRefresh()
{
   FormRun callerForm = element.args().caller();

   if (callerForm)
   {
      if (callerForm.name() == formstr(

))

     {
         callerForm.bookRefresh();
     }
   }
}

Backup SQL DB using code

DECLARE @name VARCHAR(100) — database name 
DECLARE @path VARCHAR(256) — path for backup files 
DECLARE @fileName VARCHAR(256) — filename for backup 
DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ‘G:\BACKUP\11-09-2012_2300Hrs\’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’ 
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name 
END

CLOSE db_cursor
DEALLOCATE db_cursor

MANAGEMENT REPORTER ERROR

ERROR – UNABLE TO CONTACT THE SERVER, DO YOU WANT TO CHANGE THE SERVER SETTINGS (TEST CONNECTION)

SOLUTION –

Go to SQL SERVER MANAGEMENT STUDIO> SECURITY > LOGINS> NT AUTHORITY(CHECK WHICH LOGIN YOU HAVE GIVEN WHILE INSTALLING MR) > PROPERTIES>SERVER ROLES> CHECK BOX SHOULD BE ENABLED FOR Management reporter for that particular login – ASSIGN PUBLIC + SERVER ADMIN + SYSTEM ADMIN.

Convert WorkerId to UserId

userId = DirPersonUser::worker2UserId(MyHCMWorkerRecId);

How to Publish AX Reports for a specific AOS Instance

If you are running multiple instances of AX on a single environment, you have more than likely gone through the instructions as per this URL http://technet.microsoft.com/en-us/library/hh389760.aspx.
One of the steps identified in this URL talks about publishing reports using the following AX Powershell command: publish-axreports.
What isn’t perhaps obvious from the examples shown on the Deploy reports to the new Reporting Services Instance [AX 2012] page is – How do I publish reports for a specific AOS instance? (The examples shown is for the default instance)

When you look at the full syntax of the Publish-AxReports powershell command:


You will notice two optional parameters that are of interest, namely:

• ServicesAOSName
• ServicesAOSWSDLPort

These two parameters are used to “tell” the Publish-AXReports powershell command, which AOS to connect to when publishing reports.
The syntax used for these two parameters are as follow:

• ServicesAOSName

This parameter contains the name of the server that runs the AOS instance that you want to connect to e.g. AXServer

• ServicesAOSWSDLPort

This parameter contains the WSDL Port number that has been setup for your AOS instance e.g. 8102

Now that we understand the syntax, the Publish-AXReports command would look like this:

Publish-AXReport –ReportName * -id AXSSRS –servicesAOSname AXServer –servicesAOSWSDLPort 8102
Note – If you are running the Publish-AxReport command using the –servicesAOSName parameter you’ll also need to specify the –servicesAOSWSDLPort. They are both required to ensure that command executes correctly.

Calling an OData Service From a .NET Client

AX Service start error,

Error ---
SQL diagnostics: [Microsoft][SQL Native Client][SQL Server]Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.. Connect information was: Userid = [], Database = [CN_Dev1], Server = [MDT753], DSN = [], Other = []
Object Server 01: Server main session is being destroyed.
Object Server 01: Fatal SQL condition during login. Error message: "[Microsoft][SQL Native Client][SQL Server]Login failed for user 'NT AUTHORITY\NETWORK SERVICE'."
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: ]

Resolution /Cause --
If it is a new database then check the user in security node, there must be some user that is missing from the list.Usually the users that syatem should add are network service & the machine-name$. If the users are not there, add these users in security node and in schema list.
While adding user in the user list, In new user window -> Securables page add two Stored procedures "CREATESERVERSESSIONS" & "CREATEUSERSESSIONS"  that is responsible for Server session and User Session. These can be found using search option and adding the two. Also provide execute permission on them. If this step is not taken then the AOS will not start.

To find total number of Column and Row in a DB

QUERY FOR FINDING NO OF COLUMN, NO OF ROWS IN EACH TABLE IN DB:

USE Dev_MicrosoftDynamicsAX
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY table_name asc,CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
drop table #temp;

Delete a specific Batch Job

The sql script to only delete a specific entry would be: declare @BatchJobRecId bigint select top 1 @BatchJobRecId = RECID from BATCHJOB     where CAPTION = 'Workflow message processing' and             COMPANY = 'dat' delete from BATCHJOB where RECID = @BatchJobRecId delete from BATCH where BATCHJOBID = @BatchJobRecId  if (exists(select top 1 RECID from BATCHJOB where CAPTION = 'Data upgrade')) begin     print ''     print 'There are still other btch jobs registered in AX with this name. Run again this script' end

Change table field property

static void TableFieldUpdate(Args _args)
{
    TreeNode            vendFldsRoot = treeNode::findNode(@"\Data Dictionary\Tables\ScobTopVendors\Fields");

    TreeNodeIterator    vendFldsIterator;

    TreeNode            vendFlds;

    NoYes               visibleProp;
    Table1              table1;
    Name                name;
    str property;
    ;



    vendFldsIterator = vendFldsRoot.AOTiterator();

    vendFlds = vendFldsIterator.next();



    while(vendFlds)

   {

        name = vendFlds.treeNodeName();
       select * from table1 where table1.Companyname == name;
       Property       = vendFlds.AOTgetProperties();
       Property       = SetProperty(Property, "name",table1.label);
     
           vendFlds.AOTsetProperties(Property);
           vendFlds.AOTcompile();
           vendFlds.AOTsave();
     

        vendFlds    =   vendFldsIterator.next();

    }

}

project list in ax 2012

static void listAllObjectosFromProject(Args _args)
{
    #define.Shared("private")
   
    ProjName            projName = "userlayer";
    ProjectListNode     list = infolog.projectRootNode().AOTfindChild(#Shared);
    TreeNodeIterator    ir = list.AOTiterator();
    ProjectNode         pnProj;
    ProjectNode         pn = list.AOTfindChild(projName);

    void searchAllObj(projectNode rootNode)
    {
        #TreeNodeSysNodeType
        TreeNode          childNode;
        TreeNodeIterator  rootNodeIterator;
        ;

        if (rootNode)
        {
            rootNodeIterator = rootNode.AOTiterator();
            childNode = rootNodeIterator.next();
            while (childnode)
            {
                if (childNode.treeNodeType().id() == #NT_PROJECT_GROUP)
                {
                    searchAllObj(childNode);
                }
                else
                {
                    //info(strfmt("Group :%1 - Object: %2", rootNode.AOTname(), childNode.AOTname()));
                    info(strfmt("%1$%2", rootNode.name(),childNode.AOTname()));
                }
                childNode = rootNodeIterator.next();
            }
        }
    }
    ;

    if (pn)
    {
        //info(strFmt("Project %1:", projName));
        pnProj = pn.loadForInspection();
        searchAllObj(pnProj);
        pnproj.treeNodeRelease();
    }
    else  
    {
        info("Projet objects");
    }

}