AX-SQL Tips (Data Backup)

AX-SQL Tips (Data Backup)

Maintenance Plans for Backup

Check Management – Maintenance Plan (Created by Alex)

Back-Up Maintenance Plan – There is a plan in place to do a Full backup of all User Databases including any New databases.

Purge .bak files older than 3 days.

Sp_helpdb will show all the Database backup settings whether Simple or Full.

Shrinking File Sizes

Right Click over Database – Tasks – Shrink – Change from Data to Log – Click ON

Right Click over Database - Click Properties – Click on Files Tab – Check size of File

Stored Procedure – Current Processes

Sp_who2 will show what is running but will not show which user only process owner AXAOS

Blocked Objects

Check the Blk-By column and if anything appears it is locked up. Check further down in this column and see if some other process is locked. Usually more than one lock, kill the offending process.

Microsoft Dynamics 2012 Environment Setup

Infrastructure The following diagram shows the infrastructure as it is to be deployed on customer sites implementing Dynamics AX 2012. The description for these systems follows below. Development Environment The development environment(s) is being used by developers to implement customizations and fix bugs. A development environment must have all components installed and functional that the targeted production system will have. In most cases, it is best to have these systems completely encapsulated to allow the developer using that system to quickly access all components and eventually modify, restore or remove them without the involvement of a system administrator. This allows for maximum flexibility during the actual development, but requires the developer to maintain it responsibly. Due to the integration with Team Foundation Server, a development system cannot be easily re-assigned to another developer. The development environments are the only source of any code that enters TFS. All other systems consume the code stored in TFS. QA Environment The QA environment(s) is being used by: Functional consultants to perform the pre-checkin test of customizations. Development Team Leads to perform code reviews. Solution Architects to perform code reviews. QA environments are usually re-built for the purpose of testing a single customization. The system's live-span is very short and should not be used for any other purpose but testing a single customization. TFS The Team Foundation Server (TFS) system hosts the source code control system that stores all customizations, scripts and eventually implementation data. Users do usually not have access to this system directly. Access to the system is being accomplished through the various front-end components of TFS (e.g. web access, Visual Studio, Dynamics AX). Build Environment This environment and system is used exlusively for generating builds out of the version control system and to run the initial test for these generated builds. The system is being controlled by TFS and only the technical lead would need access to this system for periodic maintenance tasks. Test Environment The test environment(s) is being used by users and/or functional consultants to test modifications undisturbed by ongoing development work. Under no circumstances should developers do development in the test environment. Debugging in a test environment can be very disruptive to other users and must only be done with prior approval from the project manager for a narrow time window and for issues that cannot be reproduced on the development environment despite multiple attempts to do so. Training Environment The training environment is used to train key and end users in preparation for the Dynamics AX 2012 introduction. The training environment is loaded with builds from the test system that are regarded stable enough to train users on them. The system must not be used to test customizations or other code changes. Sandbox Environment Sandbox environments are environments that users and/or developers can play with, modify and test new ideas on without any restrictions. Sandbox environments might be shared. In this case, the users of these systems should be considerate of others that might also be using the system. Sandbox systems should be periodically re-built. Users of these systems must not assume that changes will be kept or the systems resemble the current build status. Issues and code defects cannot be reported based on a reproduction on a Sandbox system. Configuration Environment The configuration system is optional for implementations that have a production systems set up at the beginning of the implementation. The configuration environment is being used to collect all configuration, parameter and reference data for the implementation. The test if certain data is suitable for the deployment to production must be done on other systems. Under no circumstance should any transactional data be added to this system. The system is accessible only to users that own data that is being collected on this system. Developers must not have access to this system. If code promotion is being done manually, the Technical Team Lead might require access to promote the latest builds to this system. Pre-Prod Environment The Pre-Prod system is used for the final User Acceptance Test and performance tests to be conducted on the system. Absolutely no code changes or debugging may occur on this system. Production Environment Developers should have no access to this system. Exceptions can be made to observe certain system behavior to ease the reproduction of reported issues. In these circumstances, the developer must not be able to change code artifacts of any kind, but might only observe system behavior. This system must be protected from un-authorized changes (code and data) by all possible means.

Create new Financial Dimension and use on form

Perform Below steps.

a)      Open AOT>>Data Dictionary>>Extended Data Types type/select DimensionDefault and drag it in table which will be used further as a datasource in form where you have to show the Dimensions. Do Remember  that you have to drag it in table not at DataSource.
b)     Open Table in the Data, Dictionary which will be used as a Datasource, and create a realtion with table DimensionAttributeValueSet .
c)      Right Click the Relations. Select ‘New Realation’.  Select properties. Set name as DimensionAttributeValueSet, Table as DimensionAttributeValueSet.
d)     Right Click the this newly created Relation DimensionAttributeValueSet, select New>>Normal.
e)      Set the properties of Normal Realtion as:  Field=TheFieldwhichwillsaveDimensionNumberInYourTable
Source EDT= DimensionDefault
Related Field=RecId

2.      Verify that the table that will hold the foreign key to the DimensionAttributeValueSet table is a
data source on the form(the one on which you have to show dimensions).
3.      Create a tab that will contain the financial dimensions control. This control is often the only
data shown on the tab because the number of financial dimensions can be large.
4.   set properties of Tab as under
a)      Set the Name metadata of the tab to TabFinancialDimensions.
b)     Set the AutoDeclaration metadata of the tab to Yes.
c)      Set the Caption metadata of the tab to @SYS101181 (Financial dimensions).
d)     Set the NeedPermission metadata of the tab to Manual.
e)      Set the HideIfEmpty metadata of the tab to No.
       5.  Override the pageActivated method on the new tab
public void pageActivated()
{
    dimDefaultingController.pageActivated();

    super();
}
      6.   Override the following methods on the form.
class declaration
public class FormRun extends ObjectRun
{
    DimensionDefaultingController dimDefaultingController;
}
init (for the form):
public void init()
{
    super();
    dimDefaultingController=DimensionDefaultingController::constructInTabWithValues(
      true,
      true,
      true,
      0,
      this,
      tabFinancialDimensions,
      "@SYS138487");

    dimDefaultingController.parmAttributeValueSetDataSource(myTable_ds,
    fieldstr(myTable, DefaultingDimension));
}
    7.      Override the following methods on the form data source
            public int active()
{
    int ret;
    ret = super();
    dimDefaultingController.activated();
    return ret;
}
public void write()
{
    dimDefaultingController.writing();
    super();
}
public void delete()
{
   
    super();
  dimDefaultingController.deleted();
}

SQL Database Restore Query

USE [master]
ALTER DATABASE [DB Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE DB Name FROM  DISK = N'\\File Path' WITH  FILE = 1
,  NOUNLOAD,  REPLACE,  STATS = 5

ALTER DATABASE DB Name SET MULTI_USER

GO

Get currnt company name in SSRS reports

=Microsoft.Dynamics.Framework.Reports.DataMethodUtility.
GetFullCompanyNameForUser(
Parameters!AX_CompanyName.Value,Parameters!AX_UserContext.Value)

Serial Number in SSRS ( Normal and with Group)

we can get the sequence number directly in SSRS report directly by below code in text box expression. it's basically depends on row number so you can get it from expression -   Common Function -Miscellaneous - row Number.

=RowNumber(Nothing)

The challange to use this expression is when we will use grouping in same table. Sequesnce of the number can be disturb by grouping. We can handle it by below code.

=Runningvalue(Fields!Name.value,countdistinct,"Dataset1")

** Here field name is the actual field which we are using in grouping with database name.

Address in one line in AX or SSRS

=Replace(First(Fields!VendAddress.Value, "PurchPurchaseOrderDS"),Chr(10),", ") 

Query not taking BackSlash " \ " value in AX

As AX doesn't support back slash value "\" value in X++ code but allows the same value in data base field value (In Table Field value) , So some time it's hard to get the expected value in query . I had the same problem which i got by below solution.

Problem : -

 LedgerJournalTrans hold a value with back slash (MP\PV16\000014). When i was getting the value in query to pass it in a range it was removing "\" Automatically which was the case of data mismatch and value was not coming from table. Our report was showing blank data in report.

Solution:-  

Query doesn't recognize single back slash. so we use to use double back slash "\\" to define a single slash in a value. If we are getting a from any table then we have to convert single slash in to double slash. For that we have to type four slash "\\\\".

voucher     = strReplace(contract.parmVoucher(),"\\","\\\\");

here our value (MP\PV16\000014)contain single slash  which we are replacing with double slash by four time slash. 

AX 2012 - Understanding AOT Maps with an example

Background

Maps are not new concept in software development actually. They are table wrappers to achieve general behavior. However for lame dude(s) like me, these definition words have not been enough.

Problem

The term allotted to Maps, "Map" itself is a bit confusing. But an example can really help understanding. And Maps are used everywhere, but in comparison to tables and classes ( see these building blocks are essential for any kind of most basic development), use of maps are quite rare.

Example

I have been browsing PurchTableHistory table. A method came into my consideration where I see the use of Map.  

The method, PurchTableHistory[T] >> initFromPurchTable() declares several maps based on localized buffers. Look at the following code

// Code example

PurchTableMap purchTableMap;

purchTableMap.data(_purchTable.data());

this.data(purchTableMap.data());

First, the row from PurchTable is copied to the map variable, 'purchTableMap', and then, the history buffer gets the same row but form map, and not from the original source, purchTable buffer

WHY ? Their must be a genuine need ? Why don't we copy from the original PurchTable buffer  ? Becuase we cannot :) becuase of schema change. See if most of the fields are same but not with exact field names, types and filed list count, we cannot copy a row from one table to another using xRecord[C].data() simply because of schema mismatch.

Here is where map can work, since the different name same type and purpose fields are MAPPED in the MAP :). Fields with different names from different tables are mapped to consistent names in map, so that you can use them to map one row ( from one table buffer) to another table.

One more thing, when using maps, field count doesn't matter since the map will only care and execute on the schema defined in the map. It simply would not care for schema defined in the mapped tables, it would only care for the schema defined in the MAP

How to find or create default Dimension from X++ in AX 2012

This is static method to find or create default dimension:

static DimensionDefault createDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
    DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();
    DimensionDefault                               result;
    int                                                      i;                            
    DimensionAttribute                            dimensionAttribute;
    DimensionAttributeValue                   dimensionAttributeValue;
    //_attr is dimension name in table DimensionAttribute
    container               conAttr =   _attr;
    container               conValue = _value;
    str                     dimValue;

    for (i = 1; i <= conLen(conAttr); i++)
    {
        dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));

        if (dimensionAttribute.RecId == 0)
        {
            continue;
        }

        dimValue = conPeek(conValue,i);

        if (dimValue != "")
        {
            // _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
            dimensionAttributeValue
                    dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);

            // Add the dimensionAttibuteValue to the default dimension
            valueSetStorage.addItem(dimensionAttributeValue);
        }
    }
    result = valueSetStorage.save();
    return result;
}

AX DB Restore Scripts - Moving AX DB from one server to another server

Following SQL script is very useful while moving AX DB from one server to other server. Thanks to http://www.exploreax.com.

What we are doing in below Script is - 

1. Delete SSID, domain and domain user for the Admin users. First user to login after that, will be made administrator.
2. Delete the old AOS server from AX's list of servers in SYSSERVERCONFIG , SYSSQLSETTINGS and SYSSERVERSESSIONS.

3. Delete contents of SYSBREAKPOINTLIST.


Batter to take the data from Server's table before update. 

Declare @AOS varchar(30) = '[AOSID]' --must be in the format '01@SEVERNAME'
---Reporting Services---
Declare @REPORTINSTANCE varchar(50) = 'AX'
Declare @REPORTMANAGERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/Reports_AX'
Declare @REPORTSERVERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/ReportServer_AX'
Declare @REPORTCONFIGURATIONID varchar(100) = '[UNIQUECONFIGID]'
Declare @REPORTSERVERID varchar(15) = '[DESTINATIONSERVERNAME]'
Declare @REPORTFOLDER varchar(20) = 'DynamicsAX'
Declare @REPORTDESCRIPTION varchar(30) = 'Dev SSRS';

---SSAS Services---
Declare @SSASSERVERNAME varchar(20) = '[YOURSERVERNAME]\AX'
Declare @SSASDESCRIPTION varchar(30) = 'Dev SSAS'; -- Description of the server configuration

---BC Proxy Details ---
Declare @BCPSID varchar(50) = '[BCPROXY_SID]'
Declare @BCPDOMAIN varchar(50) = '[yourdomain]'
Declare @BCPALIAS varchar(50) = '[bcproxyalias]'
---Service Accounts ---
Declare @WFEXECUTIONACCOUNT varchar(20) = 'wfexc'
Declare @PROJSYNCACCOUNT varchar(20) = 'syncex'
---Help Server URL---
Declare @helpserver varchar(200) = 'http://[YOURSERVER]/DynamicsAX6HelpServer/HelpService.svc'

---Outgoing Email Settings---
Declare @SMTP_SERVER varchar(100) = 'smtp.mydomain.com' --Your SMTP Server
Declare @SMTP_PORT int = 25
---DMF Folder Settings ---
Declare @DMFFolder varchar(100) = '\\[YOUR FILE SERVER]\AX import\ '

---Email Template Settings---
Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX Workflow QA - TESTING'
Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'workflowqa@mydomain.com'
---Email Address Clearing Settings---
DECLARE @ExclUserTable TABLE (id varchar(10))
insert into @ExclUserTable values ('userid1'), ('userid2')

--List of users separated by | to keep enabled, while disabling all others
Declare @ENABLE_USERS NVarchar(max) = '|Admin|TIM|'
--List of users separated by | to disable, while keeping all the rest enabled all others
--Declare @DISABLE_USERS NVarchar(max) = '|BOB|JANE|'

--*****BEGIN UPDATES*******---

---Update AOS Config---
delete from SYSSERVERCONFIG where RecId not in (select min(recId) from SYSSERVERCONFIG)
update SYSSERVERCONFIG set serverid=@AOS, ENABLEBATCH=1
where serverid != @AOS -- Optional if you want to see the "affected row count" after execution.

---Update Batch Servers---
delete from BATCHSERVERGROUP where RecId not in (select min(recId) from BATCHSERVERGROUP group by GROUPID)
update BATCHSERVERGROUP set SERVERID=@AOS
where serverid != @AOS -- Optional to see "affected row count"
update batchjob set batchjob.status=4 where batchjob.CAPTION = '[BATCHJOBNAME]'
update batch set batch.STATUS=4 from batch inner join BATCHJOB on BATCHJOBID=BATCHJOB.RECID AND batchjob.CAPTION = '[BATCHJOBNAME]'

---Update Reporting Services---
delete from SRSSERVERS where RecId not in (select min(recId) from SRSSERVERS)
update SRSSERVERS set
    SERVERID=@REPORTSERVERID,
    SERVERURL=@REPORTSERVERURL,
    AXAPTAREPORTFOLDER=@REPORTFOLDER,
    REPORTMANAGERURL=@REPORTMANAGERURL,
    SERVERINSTANCE=@REPORTINSTANCE,
    AOSID=@AOS,
    CONFIGURATIONID=@REPORTCONFIGURATIONID,
    DESCRIPTION=@REPORTDESCRIPTION
where SERVERID != @REPORTSERVERID -- Optional if you want to see the "affected row count" after execution.

---Update SSAS Services---
delete from BIAnalysisServer where RecId not in (select min(recId) from BIAnalysisServer)
update BIAnalysisServer set
    SERVERNAME=@SSASSERVERNAME,
    DESCRIPTION=@SSASDESCRIPTION,
ISDEFAULT = 1
WHERE SERVERNAME <> @SSASSERVERNAME -- Optional where clause if you want to see the "affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Parameters---
Update SysEmailParameters set SMTPRELAYSERVERNAME = @SMTP_SERVER, @SMTP_PORT=@SMTP_PORT
---Update DMF Settings---
update DMFParameters set SHAREDFOLDERPATH = @DMFFolder
where SHAREDFOLDERPATH != @DMFFolder --Optional to see affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Templates---
update SYSEMAILTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
update SYSEMAILSYSTEMTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
---Update User Email Addresses---
update sysuserinfo set sysuserinfo.EMAIL = '' where sysuserInfo.ID not in (select id from @ExclUserTable)

---Disable all users except for a specific set---
update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @ENABLE_USERS) = 0
---Disable specific users---
---update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @DISABLE_USERS) > 0

--Clean up server sessions
delete from SYSSERVERSESSIONS
--Clean up client sessions.
delete from SYSCLIENTSESSIONS