Missing_index in D365FO

 Refrence:

sys.dm_db_missing_index_details (Transact-SQL) - SQL Server | Microsoft Learn

SQL Delete statement with Not exists join

Sample SQL: 

Requirement: Delete with Not exsits join in where condition

DELETE  ITM FROM  InventItemInventSetup ITM 

WHERE  NOT EXISTS (SELECT 1 FROM   INVENTTABLE IT WHERE IT.DataAreaId =  ITM.DataAreaId 

AND IT.ITEMID = ITM.ITEMID)

       AND ITM.DATAAREAID in ('USMF')

SQL Keywords

STRING_AGG

SQL Query:

select STRING_AGG(erc.Code, ';') as ProductLine From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Output: 

2;7;13;G;9;3;Z;11;17;D;B;F;8;6;1;05

Execute:

Execute ('

Declare @val Varchar(MAX)

Select @val = COALESCE(@val + '','' + erc.Code, erc.Code) 

        From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Group by erc.Code order by erc.Code asc

Select @val')

COALESCE

Declare @val Varchar(MAX)

Select @val = COALESCE(@val + ';' + erc.Code, erc.Code) 

        From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Group by erc.Code order by erc.Code asc

Select @val

To select the nth row in a SQL database table?

Query:

SELECT * FROM

    (

        SELECT ROW_NUMBER () OVER (ORDER BY RecId) AS RowNum, *   FROM SALESTABLE

    ) sub

WHERE RowNum = 9

Sync D365FO/AX table to Commerce DB in D365FO

Subscribe to Event handler.

    [SubscribesTo(classStr(RetailCDXSeedDataBase), delegateStr(RetailCDXSeedDataBase, registerCDXSeedDataExtension))]

    public static void RetailCDXSeedDataBase_registerCDXSeedDataExtension(str originalCDXSeedDataResource, List resources)

    {

        if (originalCDXSeedDataResource == resourceStr(RetailCDXSeedDataAX7))

        {

            resources.addEnd(resourceStr(SAN_SalesOrderClassResource));

            resources.addEnd(resourceStr(smmBusRelSectorTableResource));

        }

    }

Create D365FO resource (SAN_SalesOrderClassResource) for new table.

Resource content: XML

<RetailCdxSeedData ChannelDBMajorVersion="7" ChannelDBSchema="ext" Name="AX7">

   <Subjobs>

       <Subjob Id="SAN_SalesOrderClass" TargetTableSchema="ext" AxTableName="SAN_SalesOrderClass">

           <ScheduledByJobs>

               <ScheduledByJob>1140</ScheduledByJob>

           </ScheduledByJobs>

           <AxFields>

              <Field Name="SALESORDERCLASS"/>

<Field Name="MAXQTY"/>

<Field Name="AVAILABLEFROM"/>

<Field Name="AVAILABLETO"/>

<Field Name="SOALLOCPRIORITY"/>

<Field Name="PAYMTERMID"/>

<Field Name="HOLDCODE"/>      

<Field Name="RECID"/>

    <Field Name="RECVERSION"/>

    <Field Name="PARTITION"/>

            </AxFields>

       </Subjob>

   </Subjobs>

</RetailCdxSeedData>

SQL Query to fetch Related information based on transaction type from TaxTrans in D365FO

//Query to Fetch Related information based on transaction type and tagged Masters (Customer/Vendor) from 

Posted Sales Tax

1. Type (Customer or vendor or ledger)

2. Identification (Customer or vendor account name or Voucher entries text)

//These columns also be converted to Computed column (T-SQL) in view.

Query:

 CAST

                 ((SELECT TOP (1) CAST((CASE WHEN tt.Voucher =

                                   (SELECT TOP (1) ct.Voucher

                                   FROM    CUSTTRANS AS ct

                                   WHERE ct.VOUCHER = tt.VOUCHER AND ct.TRANSDATE = tt.TRANSDATE AND ct.DATAAREAID = tt.DATAAREAID) THEN 'Customer' ELSE CASE WHEN tt.Voucher =

                                   (SELECT TOP (1) vt.Voucher

                                   FROM    VendTrans AS vt

                                   WHERE vt.VOUCHER = tt.VOUCHER AND vt.TRANSDATE = tt.TRANSDATE AND vt.DATAAREAID = tt.DATAAREAID) THEN 'Vendor' ELSE 'Ledger' END END) AS NVARCHAR(10)) AS Expr1

                  FROM    dbo.TAXTRANS AS tt

                  WHERE (RECID = T1.RECID)) AS NVARCHAR(60)) AS TYPE,

 

CAST

                 ((SELECT TOP (1) dpt.NAME

                  FROM    dbo.CUSTTRANS AS ct INNER JOIN

                               dbo.CUSTTABLE AS ctab ON ctab.ACCOUNTNUM = ct.ACCOUNTNUM AND ct.DATAAREAID = ctab.DATAAREAID INNER JOIN

                               dbo.DIRPARTYTABLE AS dpt ON dpt.RECID = ctab.PARTY

                  WHERE (ct.VOUCHER = T1.VOUCHER) AND (ct.TRANSDATE = T1.TRANSDATE) AND (ct.DATAAREAID = T1.DATAAREAID)

                  UNION ALL

                  SELECT TOP (1) dpt.NAME

                  FROM   dbo.VENDTRANS AS vt INNER JOIN

                               dbo.VENDTABLE AS vtab ON vtab.ACCOUNTNUM = vt.ACCOUNTNUM AND vt.DATAAREAID = vtab.DATAAREAID INNER JOIN

                               dbo.DIRPARTYTABLE AS dpt ON dpt.RECID = vtab.PARTY

                  WHERE (vt.VOUCHER = T1.VOUCHER) AND (vt.TRANSDATE = T1.TRANSDATE) AND (vt.DATAAREAID = T1.DATAAREAID)

                  UNION ALL

                  SELECT TOP (1) gjae.TEXT

                  FROM   dbo.GENERALJOURNALENTRY AS gje INNER JOIN

                               dbo.GENERALJOURNALACCOUNTENTRY AS gjae ON gjae.GENERALJOURNALENTRY = gje.RECID

                  WHERE (NOT EXISTS

                                   (SELECT TOP (1) VOUCHER

                                   FROM    dbo.CUSTTRANS AS ct2

                                   WHERE (VOUCHER = T1.VOUCHER) AND (TRANSDATE = T1.TRANSDATE) AND (DATAAREAID = T1.DATAAREAID))) AND (NOT EXISTS

                                   (SELECT TOP (1) VOUCHER

                                   FROM    dbo.VENDTRANS AS vt2

                                   WHERE (VOUCHER = T1.VOUCHER) AND (TRANSDATE = T1.TRANSDATE) AND (DATAAREAID = T1.DATAAREAID))) AND (gje.SUBLEDGERVOUCHER = T1.VOUCHER) AND (gje.ACCOUNTINGDATE = T1.TRANSDATE) AND 

                               (gje.SUBLEDGERVOUCHERDATAAREAID = T1.DATAAREAID)) AS NVARCHAR(100)) AS IDENTIFICATION 

FROM   dbo.TAXTRANS AS T1

Batch job clean up X++ D365FO

  Connection                      connection;

        Statement                       statement;

        SqlStatementExecutePermission   permission;

        str                             deleteStm;

        if (!Global::isSystemAdministrator())

        {

            error("@SYS130561");

            return;

        }

        connection  = new Connection();

        connection.ttsbegin();

        statement   = connection.createStatement();

        deleteStm = '(SELECT RECID FROM ' + ReleaseUpdateDB::backendTableName(tableNum(Batch)) + ' WHERE GROUPID = \'' + 'TT_BT' + '\')';

        deleteStm = 'DELETE FROM ' + ReleaseUpdateDB::backendTableName(tableNum(BatchConstraints)) + ' WHERE BATCHID IN ' + deleteStm +

            ' OR DEPENDSONBATCHID IN ' + deleteStm + ';';

        permission  = new SqlStatementExecutePermission(deleteStm);

        permission.assert();

        statement.executeUpdate(deleteStm);

        statement.close();

        CodeAccessPermission::revertAssert();

        statement   = connection.createStatement();

        deleteStm = 'DELETE FROM ' + ReleaseUpdateDB::backendTableName(tableNum(Batch)) + ' WHERE GROUPID = \'' + 'TT_BT' + '\';';

        permission  = new SqlStatementExecutePermission(deleteStm);

        permission.assert();

        statement.executeUpdate(deleteStm);

        statement.close();

        CodeAccessPermission::revertAssert();

statement   = connection.createStatement();

        deleteStm = 'DELETE FROM ' + ReleaseUpdateDB::backendTableName(tableNum(BatchJobHistory)) + ' WHERE STATUS != \'' + 3 + '\';';

        permission  = new SqlStatementExecutePermission(deleteStm);

        permission.assert();

        statement.executeUpdate(deleteStm);

        statement.close();

        CodeAccessPermission::revertAssert();

        connection.ttscommit();

        connection = null;

Get SQL Table row counts

CREATE TABLE TSTRowCounts(RowCount1 BIGINT,TableName VARCHAR(128))

EXEC sp_MSForEachTable 'INSERT INTO TSTRowCounts

                        SELECT COUNT_BIG(*) AS RowCount1,

                        ''?'' as TableName FROM ?'

SELECT  top 100 TableName, RowCount1  FROM  TSTRowCounts ORDER BY RowCount1 DESC

select * from TSTRowCounts

where RowCount1 >100000

Get Category Hierarchy levels X++ D365FO

 class GetCategoryLevels

{

    public static void main(Args _args)

    {

// TOP to BOTTOM hierarchy level data

        void loopchild(RefRecId     _recId)

        {

            EcoResCategory   ecoResCategoryChk, ecoResCategoryChild;

            while select ecoResCategoryChk

                where ecoResCategoryChk.ParentCategory ==  _recId

            {

                //Child query insert to table Write ur logic

                Info(ecoResCategoryChk.Name);

                ecoResCategoryChild.clear();

                select firstonly  ecoResCategoryChild

                    where ecoResCategoryChild.ParentCategory ==  ecoResCategoryChk.RecId;

                if(ecoResCategoryChild)

                {

                    loopchild(ecoResCategoryChild.ParentCategory);

                }

            }

        }

        EcoResCategory  ecoResCategory;

        EcoResProductCategory   ecoResProductCategory, ecoResProductCategoryCHk;

        RefRecId parentCategory;

        select firstonly  ecoResCategory

            where ecoResCategory.Name == "Refrigeration";

        parentCategory = EcoResCategory.RecId;

        if(parentCategory)

        {

            //Parent query insert  - Write ur logic

            Info(ecoResCategory.Name);

            loopchild(parentCategory);

        }

    }

}

Insert table data from one DB to another DB SQL script

 Insert table data from one DB to another DB SQL script

SQL 1:

INSERT INTO <Target DB>.dbo.<Table Name>

SELECT *

FROM <Source DB>.dbo.<Table Name>

where <Source DB>.dbo.<Table Name>.<Table field name>= '<values>'

 

SQL 2:

INSERT INTO [target db].[dbo].[table] (<columnA>,<columnB>,…)

SELECT <columnA>,<columnB>,…

FROM [source db].[dbo].[table] 

 

SysDa API in D365 FO

SysDa API

"Da" is short for Data access. It is a set of new APIs exposing the object graph that the compiler otherwise produces from select statements. 

Problem:
On the extensibility journey in D365 FO, X++ select statements are not extensible. If developer needs to add a new range, a join, a field in the field list – it is not possible.

Pros:
1. It has the same performance characteristics as select statements
2. It is extensible for futher enhancement

3. It is available in from PU22

List:

• Select: SysDaQueryObject, SysDaSearchObject, and SysDaSearchStatement
• Update: SysDaUpdateObject and SysDaUpdateStatement
• Insert: SysDaInsertObject and SysDaInsertStatement
• Delete: SysDaQueryObject, SysDaDeleteObject, and SysDaDeleteStatement

CRUD Operation:

Select: 

        //Selection - On SYSDA API  

        VendTable   vendTab; 

        var qe = new SysDaQueryObject(vendTab);

        qe.projection()

                .add(fieldStr(VendTable, AccountNum))

                .add(fieldStr(VendTable, VendGroup));

        qe.WhereClause(new SysDaEqualsExpression(

                new SysDaFieldExpression(vendTab, fieldStr(VendTable, VendGroup)),

                new SysDaValueExpression(10)));

        //below statement to get less than or equal to field values

        //qe.WhereClause(new SysDaLessThanOrEqualsExpression(

        //        new SysDaFieldExpression(vendTab, fieldStr(VendTable, VendGroup)),

        //        new SysDaValueExpression(10)));

        //Default order by field is ASC

        qe.OrderByClause().addDescending(fieldStr(VendTable, AccountNum)); //for desc

        //Type 1

        var so = new SysDaSearchObject(qe);

        var ss = new SysDaSearchStatement();

        while (ss.nextRecord(so))

        {

            info(vendTab.AccountNum);

        }

        //Type 2 

        var fo = new SysDaFindObject(qe);

        new SysDaFindStatement().execute(fo);

        //Type 3 Using join

        PurchLine   purchLine;

        InventDim   Invdim;

        var qepl = new SysDaQueryObject(purchLine);

        var qedim = new SysDaQueryObject(Invdim);

        qepl.joinClause(SysDaJoinKind::InnerJoin,qedim);

        //Relation manual

        qedim.WhereClause(new SysDaEqualsExpression(

                new SysDaFieldExpression(purchLine, fieldStr(PurchLine, InventDimId)),

                new SysDaFieldExpression(Invdim, fieldStr(InventDim, InventDimId))));

        qepl.WhereClause(new SysDaEqualsExpression(

                new SysDaFieldExpression(purchLine, fieldStr(PurchLine, VendAccount)),

                new SysDaValueExpression("000013")));

        var so1 = new SysDaSearchObject(qepl);

        var ss1 = new SysDaSearchStatement();

        while (ss1.nextRecord(so1))

        {

            info(purchLine.ItemId + purchLine.InventDimId);

        }

Update:

      // Update - ON SysDa API

        PurchTable  purchTable;

        var updateObj = new SysDaUpdateObject(purchTable);

        updateObj.settingClause()

         .add(fieldStr(PurchTable, PurchName), new SysDaValueExpression( "SAN")); 

        updateObj.whereClause(new SysDaEqualsExpression(

        new SysDaFieldExpression(purchTable, fieldStr(PurchTable, OrderAccount)),

            new SysDaValueExpression("000013")));

        //Updating the rows.

        ttsbegin;

        new SysDaUpdateStatement().execute(updateObj);

        ttscommit;

Insert:

     // Insert - ON SysDa API

        sanLanguageTable santab;

        var Io = new SysDaInsertObject(santab);

        Io.fields()

            .add(fieldStr(sanLanguageTable, Name))

            .add(fieldStr(sanLanguageTable, Description));

        VendGroup source;

        var qe = new SysDaQueryObject(source); 

        var s1 = qe.projection()

            .Add(fieldStr(VendGroup, Name))

            .Add(fieldStr(VendGroup, VendGroup));

        Io.query(qe);

        var istate = new SysDaInsertStatement();

        ttsbegin;

        istate.executeQuery(Io);

        ttscommit;

Delete:

     // Delete - ON SysDa API

        sanLanguageTable santab;

        var qe = new SysDaQueryObject(santab); 

        var s = qe.projection()

                .add(fieldStr(sanLanguageTable, Name));

        var ds = new SysDaDeleteStatement();

        var delobj = new SysDaDeleteObject(qe);

        ttsbegin;

        ds.executeQuery(delobj);

        ttscommit;

        //To get no of rrows after deletion

        info("Number of rows after deletion: " + any2Str(t.RowCount()));

Important:

You can use the toString() method on SysDaQueryObject, SysDaUpdateObject, SysDaInsertObject, and SysDaQueryObject objects to view the statement that you're building.

Related Objects: (SysDa API)

Base Enum:

SysDaAggregateFieldType

SysDaFirstOnlyHint

SysDaJoinKind

Class:

SysDaAggregateProjectionField

SysDaAndExpression

SysDaAvgOfField

SysDaBinaryExpression

SysDaCountOfField

SysDaCrossCompany

SysDaCrossCompanyAll

SysDaCrossCompanyContainer

SysDaDataAccessStatement

SysDaDeleteObject

SysDaDeleteStatement

SysDaDivideExpression

SysDaEqualsExpression

SysDaFieldExpression

SysDaFindStatement

SysDaGreaterThanExpression

SysDaGreaterThanOrEqualsExpression

SysDaGroupBys

SysDaInsertObject

SysDaInsertStatement

SysDaIntDivExpression

SysDaLessThanExpression

SysDaLessThanOrEqualsExpression

SysDaLikeExpression

SysDaMaxOfField

SysDaMinOfField

SysDaMinusExpression

SysDaModExpression

SysDaMultiplyExpression

SysDaNotEqualsExpression

SysDaOrderBys

SysDaOrExpression

SysDaPlusExpression

SysDaProjectionField

SysDaQueryExpression

SysDaQueryObject

SysDaSearchObject

SysDaSearchStatement

SysDaSelection

SysDaSettingsList

SysDaSumOfField

SysDaUpdateObject

SysDaUpdateStatement

SysDaValueExpression

SysDaValueField

Get all records from AOT Query by enabling validTimeStateDateTimeRange in D365 FO/AX 2012

utcdatetime minDateTime = DateTimeUtil::minValue() , maxDateTime = DateTimeUtil::maxValue();


qry = new Query(QueryStr(TestQuery));
qry.validTimeStateDateTimeRange(minDateTime, maxDateTime); //In order display all records from ValidTimeState stamp table

Example: (Table Names)
HcmEmployment
HcmWorkerPositionAssignment
HcmWorkerEnrollBenefit

Move LCS implementation projects to different Azure AD tenants

Reference: https://docs.microsoft.com/en-us/dynamics365/unified-operations/fin-and-ops/get-started/move-lcs-implementation-project-tenant

Document Routing Agent D365FO

Document Routing Agent is an application which enables network printing scenarios in Dynamics 365 for Finance and Operations. It basically manages the spooling of documents to network printer devices.

Install/Configure Document Routing Agent:

Navigate for network printers page 

-> Organization administration > Setup > Network printers

       -> Options tab, in the Application group, 

       -> click -> Download document routing agent installer.

-> Once downloaded an executable file double click to install it on system.

-> Close all browser

-> Run -> Document Routing Agent

      -> Click -> Setting 

                  -> Enter Application Id, AAD tenant, D365 URL

-> Sign In Document Routing Agent using Credentials

-> On Document Routing Agent

      -> Click -> Printers

      -> Navigate to Network Printer

      -> Edit and Make Active to YES for necessary printers

Note:

'Microsoft Dynamics 365 Document Routing Service' service should run under "Domain admin user"

Ref: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/install-document-routing-agent

Import New user using SQL in Azure cloud DEV VM in D365 FO

INSERT INTO USERINFO(Id, Name, ENABLE, COMPANY,SID,NETWORKDOMAIN, NETWORKALIAS, ENABLEDONCE,LANGUAGE,
 HELPLANGUAGE, PREFERREDTIMEZONE, ACCOUNTTYPE,DEFAULTPARTITION)
VALUES ('<UserId>','<Name>',1,'<Default company login>','<SID>',
'https://sts.windows.net/',
'<Email Id>',1,'en-us','en-us',<Timezone>,2,1);

Insert into SECURITYUSERROLE (USER_, SECURITYROLE, ASSIGNMENTSTATUS, ASSIGNMENTMODE)
values ('<UserId>','199',1,1),
 ('<UserId>','217',1,1);

Step:

Method 1:
1. Right click on AOT -> Refresh
2. Full DB sync
3. iisreset

Method 2: (Method 1 is not working)
1. Right click on AOT -> Refresh
2. Build App Suite model with DB sync
3. iisreset

Method 3: (if Method 1 & 2 is not working)
1. Right click on AOT -> Refresh
2. Full Build All model with DB sync
3. iisreset

Timezone and TZId field in Dynamics

Timezone description	TimeZone	TZId
(GMT-12:00) International Date Line West	24	24001
(GMT-11:00) Midway Island, Samoa	65	65001
(GMT-10:00) Hawaii	39	39001
(GMT-09:00) Alaska	2	2001
(GMT-08:00) Pacific Time (US & Canada)	58	58001
(GMT-08:00) Tijuana, Baja California	59	59001
(GMT-07:00) Arizona	75	75001
(GMT-07:00) Mountain Time (US & Canada)	47	47001
(GMT-07:00) Chihuahua, La Paz, Mazatlan	48	48001
(GMT-06:00) Central America	15	15001
(GMT-06:00) Central Time (US & Canada)	21	21001
(GMT-06:00) Guadalajara, Mexico City, Monterrey	22	22001
(GMT-06:00) Saskatchewan	11	11001
(GMT-05:00) Bogota, Lima, Quito, Rio Branco	63	63001
(GMT-05:00) Eastern Time (US & Canada)	29	29001
(GMT-05:00) Indiana (East)	74	74001
(GMT-04:00) Atlantic Time (Canada)	6	6001
(GMT-04:00) La Paz	64	64001
(GMT-04:00) Manaus	17	17001
(GMT-04:00) Santiago	57	57001
(GMT-04:30) Caracas	85	85001
(GMT-03:30) Newfoundland	54	54001
(GMT-03:00) Brasilia	28	28001
(GMT-03:00) Buenos Aires, Georgetown	62	62001
(GMT-03:00) Greenland	36	36001
(GMT-03:00) Montevideo	83	83001
(GMT-02:00) Mid-Atlantic	45	45001
(GMT-01:00) Azores	10	10001
(GMT-01:00) Cape Verde Is.	12	12001
(GMT) Casablanca, Monrovia, Reykjavik	37	37001
(GMT) Greenwich Mean Time : Dublin, Edinburgh, Lisbon, Londo	35	35001
(GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna	79	79001
(GMT+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Pragu	18	18001
(GMT+01:00) Brussels, Copenhagen, Madrid, Paris	60	60001
(GMT+01:00) Sarajevo, Skopje, Warsaw, Zagreb	19	19001
(GMT+01:00) West Central Africa	78	78001
(GMT+02:00) Amman	43	43001
(GMT+02:00) Athens, Bucharest, Istanbul	38	38001
(GMT+02:00) Beirut	46	46001
(GMT+02:00) Minsk	27	27001
(GMT+02:00) Cairo	30	30001
(GMT+02:00) Harare, Pretoria	68	68001
(GMT+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius	33	33001
(GMT+02:00) Jerusalem	42	42001
(GMT+02:00) Windhoek	51	51001
(GMT+03:00) Baghdad	5	5001
(GMT+03:00) Kuwait, Riyadh	3	3001
(GMT+03:00) Moscow, St. Petersburg, Volgograd	61	61001
(GMT+03:00) Nairobi	25	25001
(GMT+03:00) Tbilisi	34	34001
(GMT+03:30) Tehran	41	41001
(GMT+04:00) Abu Dhabi, Muscat	4	4001
(GMT+04:00) Baku	9	9001
(GMT+04:00) Caucasus Standard Time	84	84001
(GMT+04:00) Yerevan	13	13001
(GMT+04:30) Kabul	1	1001
(GMT+05:00) Ekaterinburg	31	31001
(GMT+05:00) Islamabad, Karachi, Tashkent	80	80001
(GMT+05:30) Chennai, Kolkata, Mumbai, New Delhi	40	40001
(GMT+05:30) Sri Jayawardenepura	69	69001
(GMT+05:45) Kathmandu	52	52001
(GMT+06:00) Almaty, Novosibirsk	50	50001
(GMT+06:00) Astana, Dhaka	16	16001
(GMT+06:30) Yangon (Rangoon)	49	49001
(GMT+07:00) Bangkok, Hanoi, Jakarta	66	66001
(GMT+07:00) Krasnoyarsk	56	56001
(GMT+08:00) Beijing, Chongqing, Hong Kong, Urumqi	23	23001
(GMT+08:00) Irkutsk, Ulaan Bataar	55	55001
(GMT+08:00) Kuala Lumpur, Singapore	67	67001
(GMT+08:00) Perth	77	77001
(GMT+08:00) Taipei	70	70001
(GMT+09:00) Osaka, Sapporo, Tokyo	72	72001
(GMT+09:00) Seoul	44	44001
(GMT+09:00) Yakutsk	82	82001
(GMT+09:30) Adelaide	14	14001
(GMT+09:30) Darwin	7	7001
(GMT+10:00) Brisbane	26	26001
(GMT+10:00) Canberra, Melbourne, Sydney	8	8001
(GMT+10:00) Guam, Port Moresby	81	81001
(GMT+10:00) Hobart	71	71001
(GMT+10:00) Vladivostok	76	76001
(GMT+11:00) Magadan, Solomon Is., New Caledonia	20	20001
(GMT+12:00) Auckland, Wellington	53	53001
(GMT+12:00) Fiji, Kamchatka, Marshall Is.	32	32001
(GMT+13:00) Nuku’alofa	73	73001

Explore metadata search feature in Visual Studio D365 FO

Access the Metadata search tool window from the Dynamics 365 > Metadata Search.

Search Keyword Description Code # Search for a specific code. Use quotes around code snippets. The matching source code is the elements that contain the specified code snippet. Type # Filter the elements by type  Each comma-separated value should be the name of an element or sub-element type (root type or subtype) (i.e. table, class, field). Logic of filtering is: Model # Filter the elements by model Each comma-separated value should be the name of a model in your application Name # Filter the elements by name This is the default filter, meaning if you just type a filter value, it is assumed to be an element name. Each comma-separated value is an acceptable element name. Property # Search for element with the property with the specific value  Each comma-separated value should be in the form property_name=property_value

Note: Make sure there should not be any space in between Type values otherwise it will show "Invalid query" error.

Reference: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/dev-tools/metadata-search-visual-studio 

Cleaning up cross-reference DB in Dynamics 365 for finance and operation ON DEV VM

// For unwanted cross reference data clean Up on DB (DYNAMICSXREFDB)

DELETE [REFERENCES] FROM [REFERENCES]

    JOIN Names 

ON (Names.Id = [REFERENCES].SourceId 

OR Names.Id = [REFERENCES].TargetId)

    JOIN Modules 

ON Names.ModuleId = Modules.Id

Turn off all the D365F&O services in DEV VM

To turn off all the D365F&O services

Steps:
1. Open PowerShell as an admin
2. Type "stop-d365environment"
3. click -> Enter


To start
Start-d365environment

Backup and restore in another ENV Dynamics 365 Finance and operations

1.       Take the AxDB database backup

2.       Do the Full Build.

3.       Do the Database Synchronize 

4.       Connect the LCS and download the Database 

5.       Create the new Database with Name : UATBackUp81.

6.       Restore the downloaded Database from LCS to UATBackUp81.

7.       Need to change the UATBackUp81

a.        Security User details

Script:

CREATE USER axdeployuser FROM LOGIN axdeployuser

EXEC sp_addrolemember 'db_owner', 'axdeployuser'

CREATE USER axdeployextuser WITH PASSWORD = '<password from LCS>'

IF EXISTS (select * from sys.database_principals where type = 'R' and name = 'DeployExtensibilityRole')

BEGIN

    EXEC sp_addrolemember 'DeployExtensibilityRole', 'axdeployextuser'

END

CREATE USER axdbadmin WITH PASSWORD = '<password from LCS>'

EXEC sp_addrolemember 'db_owner', 'axdbadmin'

CREATE USER axruntimeuser WITH PASSWORD = '<password from LCS>'

EXEC sp_addrolemember 'db_datareader', 'axruntimeuser'

EXEC sp_addrolemember 'db_datawriter', 'axruntimeuser'

CREATE USER axmrruntimeuser WITH PASSWORD = '<password from LCS>'

EXEC sp_addrolemember 'ReportingIntegrationUser', 'axmrruntimeuser'

EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'

EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'

CREATE USER axretailruntimeuser WITH PASSWORD = '<password from LCS>'

EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'

EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'

CREATE USER axretaildatasyncuser WITH PASSWORD = '<password from LCS>'

EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'

ALTER DATABASE SCOPED CONFIGURATION  SET MAXDOP=2

ALTER DATABASE SCOPED CONFIGURATION  SET LEGACY_CARDINALITY_ESTIMATION=ON

ALTER DATABASE SCOPED CONFIGURATION  SET PARAMETER_SNIFFING= ON

ALTER DATABASE SCOPED CONFIGURATION  SET QUERY_OPTIMIZER_HOTFIXES=OFF

ALTER DATABASE <imported database name> SET COMPATIBILITY_LEVEL = 130;

ALTER DATABASE <imported database name> SET QUERY_STORE = ON;

update [dbo].[SYSSERVICECONFIGURATIONSETTING]

set value ='<tenant ID from existing database>'

where name = 'TENANTID'

update dbo.POWERBICONFIG

set TENANTID = '<tenant ID from existing database>'

update dbo.PROVISIONINGMESSAGETABLE

set TENANTID = '<tenant ID from existing database>'

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

8.       Changes the names like AxDB to AxDB_standard ,  UATBackUp81 to AXDB

USE master;

GO 

ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE AxDB MODIFY NAME = AxDB_Standard ;

GO 

ALTER DATABASE AxDB_ Standard SET MULTI_USER

GO

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

USE master;

GO 

ALTER DATABASE UATBackUp81 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE UATBackUp81 MODIFY NAME = AxDB ;

GO 

ALTER DATABASE AxDB SET MULTI_USER

GO

9.       Do the Full DB Synchronize