D365 FO DB restore in to cloud hosted Development environment using backup (.bacpac) file

 DB restore in to cloud hosted environment.

 

Import Database from UAT/Production to cloud-hosted or dev environment | D365 Finance and operations

 

To import a database to a developer environment after you've downloaded a database backup (.bacpac) file, you can begin the manual import operation on your Tier 1 environment. When you import the database, we recommend that you follow these guidelines:

 

·        Keep a copy of the existing AxDB database, so that you can revert to it later if needed.

·        Import the new database under a new name, such as AxDB_fromProd.

 

To ensure the best performance, copy the *.bacpac file to the local computer that you're importing from. Download sqlpackage .NET Core for Windows from Get sqlpackage .NET Core for Windows. Open a Command Prompt window, and run the following commands from the sqlpackage .NET Core folder.

 Note: 

Stop all the related dynamics services and w3 services.

SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200  (old)

or 

SqlPackage /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"AdventureWorksLT" /TargetUser:"your_username" /TargetPassword:"your_password" /TargetTrustServerCertificate:True /SourceFile:"C:\AdventureWorksLT.bacpac" (new)

Eg: 

SqlPackage.exe /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"NDPC" /TargetTrustServerCertificate:True /SourceFile:"J:\MSSQL_BACKUP\ERPREPROD.bacpac" /p:CommandTimeout=6200

 

·        tsn (target server name) – The name of the Microsoft SQL Server instance to import into.

·        tdn (target database name) – The name of the database to import into. The database should not already exist.

·        sf (source file) – The path and name of the file to import from.

Eg: 

SqlPackage.exe /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"NDPC" /TargetTrustServerCertificate:True /SourceFile:"J:\MSSQL_BACKUP\DFDSPRODCOPY.bacpac" /p:CommandTimeout=6200

(https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver15)

Update the database

Run the following SQL script against the imported database. This script adds back the users that you deleted from the source database and correctly links them to the SQL logins for this SQL Server instance. The script also turns change tracking back on. Remember to edit the final ALTER DATABASE statement so that it uses the name of your database.

CREATE USER axdeployuser FROM LOGIN axdeployuser

EXEC sp_addrolemember 'db_owner', 'axdeployuser'

 

CREATE USER axdbadmin FROM LOGIN axdbadmin

EXEC sp_addrolemember 'db_owner', 'axdbadmin'

 

CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser

EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'

EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'

 

CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser

 

CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser

 

CREATE USER axdeployextuser FROM LOGIN axdeployextuser

 

CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]

EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'

 

UPDATE T1

SET T1.storageproviderid = 0

    , T1.accessinformation = ''

    , T1.modifiedby = 'Admin'

    , T1.modifieddatetime = getdate()

FROM docuvalue T1

WHERE T1.storageproviderid = 1 --Azure storage

 

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2

GO

-- Begin Refresh Retail FullText Catalogs

DECLARE @RFTXNAME NVARCHAR(MAX);

DECLARE @RFTXSQL NVARCHAR(MAX);

DECLARE retail_ftx CURSOR FOR

SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) fullname FROM SYS.FULLTEXT_INDEXES

    WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1 FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME = 'COMMERCEFULLTEXTCATALOG');

OPEN retail_ftx;

FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

 

BEGIN TRY

    WHILE @@FETCH_STATUS = 0

    BEGIN

        PRINT 'Refreshing Full Text Index ' + @RFTXNAME;

        EXEC SP_FULLTEXT_TABLE @RFTXNAME, 'activate';

        SET @RFTXSQL = 'ALTER FULLTEXT INDEX ON ' + @RFTXNAME + ' START FULL POPULATION';

        EXEC SP_EXECUTESQL @RFTXSQL;

        FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

    END

END TRY

BEGIN CATCH

    PRINT error_message()

END CATCH

 

CLOSE retail_ftx;

DEALLOCATE retail_ftx;

-- End Refresh Retail FullText Catalogs

 

--Begin create retail channel database record--

declare @ExpectedDatabaseName nvarchar(64) = 'Default';

declare @DefaultDataGroupRecId BIGINT;

declare @ExpectedDatabaseRecId BIGINT;

IF NOT EXISTS (select 1 from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName)

BEGIN

               select @DefaultDataGroupRecId = RECID from RETAILCDXDATAGROUP where NAME = 'Default';

               insert into RETAILCONNDATABASEPROFILE (DATAGROUP, NAME, CONNECTIONSTRING, DATASTORETYPE)

               values (@DefaultDataGroupRecId, @ExpectedDatabaseName, NULL, 0);

               select @ExpectedDatabaseRecId = RECID from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName;

               insert into RETAILCDXDATASTORECHANNEL (CHANNEL, DATABASEPROFILE)

               select RCT.RECID, @ExpectedDatabaseRecId from RETAILCHANNELTABLE RCT

               inner join RETAILCHANNELTABLEEXT RCTEX on RCTEX.CHANNEL = RCT.RECID

        update RETAILCHANNELTABLEEXT set LIVECHANNELDATABASE = @ExpectedDatabaseRecId where LIVECHANNELDATABASE = 0

END;

--End create retail channel database record

 

Turn on change tracking

If change tracking was turned on in the source database, be sure to turn it on in the newly provisioned database in the target environment. To turn on change tracking, use the ALTER DATABASE command.

ALTER DATABASE [your database name] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON);

Start to use the new database

To switch environments and use the new database, first stop the following services:

·        World Wide Web Publishing Service

·        Microsoft Dynamics 365 Unified Operations: Batch Management Service

·        Management Reporter 2012 Process Service

After these services have been stopped, rename the AxDB database AxDB_orig, rename your newly imported database AxDB, and then restart the three services.

To switch back to the original database, reverse this process. In other words, stop the services, rename the databases, and then restart the services.

 

Example

 

ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE AxDB MODIFY NAME = AxDB_orig

GO

ALTER DATABASE AxDB_orig SET MULTI_USER;

GO

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

USE master;

ALTER DATABASE AXDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE AXDB MODIFY NAME = AXDB_Orig;

ALTER DATABASE AXDB_Orig SET MULTI_USER;

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

 

MS reference :

https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-exportuat#import-the-database

  

Override An Existing Lookup Method: Chain Of Command D365 X++

 

Override An Existing Lookup Method: Chain Of Command

Here in this example, we will see, how we can override the form control lookup method using a chain of commands.

Let's take an example of the Purch order form and we will try to override the punchline item ID control lookup.

1. Step one create new Classes as shown below.

[ExtensionOf(FormStr(PurchTable))]

final class PurchTableItemIdLookup_Extension

{

   

}

2. Step two, create a new method with customized logic to override the standard lookup. I call this function "OverrideItemIdLookup" as shown below.

    public void overrideItemIdLookup(FormStringControl _formControl)

    {

        Query   qr = new Query();

        QueryBuildDataSource    qbdsInventTableExpanded;

        QueryBuildDataSource    qbdEcoResLS;

        SysTableLookup systablelookup = SysTableLookup::newParameters(tableNum(InventTable),_formControl);

        qbdsInventTableExpanded = qr.addDataSource(tableNum(InventTable));

        qbdEcoResLS = qr.dataSourceTable(tableNum(InventTable)).addDataSource(tableNum( EcoResProductLifecycleState))   ;

        qbdEcoResLS.addLink(fieldNum(InventTable, ProductLifeCycleStateId), fieldNum(EcoResProductLifecycleState, StateId));

        qbdEcoResLS.addRange(fieldNum(EcoResProductLifecycleState, ItemsHideDropDownPurch)).value(queryValue(NoYes::No));

       

        systablelookup.parmQuery(qr);

        systablelookup.addLookupfield(fieldNum(InventTable,ItemId));

        systablelookup.addLookupfield(fieldNum(InventTable,NameAlias));

        systablelookup.performFormLookup();

    }

3. step three, this is the Main point, as we have created a new method, the same is to be registered. for doing this, we need to override the Init method as shown below.

    public void init()

    {

        next init();

//<<controlname. -->PurchLine_ItemId

        PurchLine_ItemId.registerOverrideMethod(methodStr(FormDataObject,lookup), FormMethodStr(PurchTable, overrideItemIdLookup));

    }

Now build and run.. 

Calling REST API from AX 2012

As we know, JSON is not supported by AX 2012, and calling REST API is challenging.  The alternate way is to use XML webRequest below is the code, which calls the API and gets the response as a string. The code also reads the response and displays it in the info log.

 Here is the code to call REST API from AX 2012

static void Send_RestAPI(Args _args)

{

    System.IO.Stream streamstr,responsestr;

    System.IO.StreamWriter streamWriter;

    System.Net.HttpWebRequest request;

    System.Net.WebHeaderCollection headers;

    System.Net.HttpWebResponse response;

    System.IO.StreamReader reader;

    str wsResponse;

    str xml;

    str                                 xmlOut;

    //

    XmlDocument     xmlDocument;

    XmlNodeList     xmlScriptList;

    XmlNodeList     xmlResponseList;

    XmlElement      nodeScript;

    XmlElement      nodeResponse;

    XMLParseError   xmlError;

     // Define temporary variables

    str _xmlMsg;

    int i, j;

    ;

    new InteropPermission(InteropKind::ClrInterop).assert();

    try

    {

        // request url

        request = System.Net.WebRequest::Create('https://api.xxxxxx.net/1.0/Tokenxxx') as  System.Net.HttpWebRequest;

        // http method

        request.set_Method('Post');

        // headers

        request.set_ContentType("application/xml; charset=utf-8");

        // request body

         xml =

            "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +

            "<RequestToken xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +

            "<grant_type>asaasass</grant_type>" +

            "<username>asasadsdsdd</username>" +

            "<password>******</password>" +

            "</RequestToken>";

        streamstr = request.GetRequestStream();

        streamWriter = new System.IO.StreamWriter(streamstr);

        streamWriter.Write(xml);

        streamWriter.Flush();

        streamWriter.Close();

        streamWriter.Dispose();

        // response

        response = request.GetResponse();

        reader = new System.IO.StreamReader(response.GetResponseStream());

        wsResponse = reader.ReadToEnd();

       // --------------------- READ FROM XML-----------------

       xmlDocument             = XmlDocument::newXml(wsResponse);

         // Get the root element and its child nodes

       nodeScript = xmlDocument.getNamedElement("Tokenxx");

       xmlScriptList = nodeScript.childNodes();

       for(i=0; i < xmlScriptList.length(); i++)

        {

            nodeResponse = xmlScriptList.item(i);

            if (nodeResponse.nodeName() == 'rexxxToken')

            {

                 info(strFmt("rToken: %1", nodeResponse.text()));

            }

            if (nodeResponse.nodeName() == 'axxxtoken')

            {

                info(strFmt("axxxtoken: %1", nodeResponse.text()));

            }

            if (nodeResponse.nodeName() == 'expiresIn')

            {

                  info(strFmt("exxxIn: %1", nodeResponse.text()));

            }

            //xmlResponseList = nodeResponse.childNodes();

            //if(nodeResponse.selectSingleNode('refreshToken'))

          /*  if (i == 0)

                info(strFmt("refreshToken: %1", nodeResponse.text()));//innerText()));//text()));

            if (i == 1)

                 info(strFmt("access_token: %1", nodeResponse.text()));

            if (i == 2)

                 info(strFmt("expiresIn: %1", nodeResponse.text()));*/

        }

        // --------------------- READ FROM XML-----------------

        //

        reader.Close();

        //info(wsResponse);

    }

    catch (Exception::CLRError)

    {

        throw error(AifUtil::getClrErrorMessage());

    }

    CodeAccessPermission::revertAssert();

}

Get default dimension from GeneralJournalAccountEntry

Here we will see how to get the default Dimension from the table GeneralJournalAccountEntryEntity.

1. Add DimensionCombinationEntity as DS 

2. Add a relation with LedgerDimension and Recid as shown below.

3. Add the field Display value from DimensionCombination DS. This will give you the Default Dimension for GeneralJournalAccountEntryEntity.

 

Dynamic query ranges to run queries in Microsoft Dynamics AX

 Use the standard flexibility of the queries

The standard queries already support a level of flexibility with predefined functions. You can read more about that in this blog post: 

http://daxmusings.codecrib.com/2013/07/custom-query-range-functions-using.html

The class “SysQueryRangeUtil” contains several predefined methods which can be used to make your query more dynamic. The format is plain and simple: ( some code ), you only need the parentheses around the method.

Just to explain how this can be used, here are some samples:

Many other scenarios are possible. This second option gives you the ultimate flexibility to create dynamic queries.

Data entity error "Change Tracking Query error in D365 Finance and Operations Data Export" D365FO

Change Tracking Query error in D365 Finance and Operations Data Export

Recently there was an issue with Change Tracking for BYOD. We keep getting the following error even after Change Tracking is enabled for the data entity.

"Incremental push is not supported for entity X, as change tracking query is not enabled on it."

Solution :

After debugging I found that the system will check if the record exists in the table "AifSqlCdcEnabledTables" with the field TableName and return int (1/0).

Run the below query in SQL to find if the list contains your table.

 select RecId, * from AifSqlCdcEnabledTables       where AifSqlCdcEnabledTables.TableName = 'XXXXX'

In my case, the Primary table was missing and causing the issue, whereas the secondary tables are present in the list. So I have changed the Entity to make the Primary Data source as secondary and secondary as Primary and the issue is fixed.

SQL Command to Enable and Disable Change Tracking (SQL Server) + D365 FO

 Below are the commands used to enable/disable change tracking in the SQL. 

1. Enable change tracking on the database

ALTER DATABASE AXDBNAME  

SET CHANGE_TRACKING = ON  

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  

2. Enable Change Tracking for a Table

ALTER TABLE TableName

ENABLE CHANGE_TRACKING  

WITH (TRACK_COLUMNS_UPDATED = ON)

3. Disable Change Tracking for a Database 

ALTER DATABASE AdventureWorks2012  

SET CHANGE_TRACKING = OFF

4. Disable Change Tracking for a Table

ALTER TABLE TableName  

DISABLE CHANGE_TRACKING;