Index on table is disabled or resides in a filegroup which is not online

Hello Everyone,

We have seen a problem while testing standard or custom functionality in T-1 box after database restore from PROD and deployed custom package which thrown a SQL server error as  “Index on table is disabled or resides in a filegroup which is not online.”  And the same issue observed at many functional processes.

Use the standard docs LINK to Export a copy of the standard user acceptance testing (UAT) database (.bacpac).

 And use the LINK for Import of azure SQL backup(.bacpac) to your T-1 D365 environment.

 As we know azure SQL backup (.bacpac) cannot be directly imported to T-1 box as T-1 has on-premise SQL server which accepts only (.bak) backup files. For the same we have to convert it from (.bacpac) to (.bak) using sqlpackage .NET Core and below commands.

 SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200 /TargetUser:”axdbadmin” /TargetPassword:”AOSWebSite@123″ /TargetTrustServerCertificate:True

While conversion is happening using the SQLPackage, it does lot of steps and this process takes couple of hours depending on the database size and the system configuration where you are executing this process.

I have seen a problem recently, where the re-indexing step got failed / not executed completely because of the VM got shutdown, in-between and the same backup was been taken to use in T-1 box where i have seen couple of issues as like below.

1. Cannot insert a record in SysGlobalConfiguration table as record already exists.
2. [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Index “I_6941TTSITEMDIMIDX” on table “INVENTSUMDELTADIM” (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

After analysis on the converted database found that many table indexes were in disabled status and so when d365 application executes a selected statement where the index is used with through error as mentioned above in point 2.

To resolve this issue, you run reindexing on the database where you face the problem. But make sure you do fresh conversion of the *backpac file to complete successfully so that the rebuilding indexes step also completed.

Thanks 

Happy Learning!

Add or Remove fields to be available by default while using the Excel add-ins

Hello Folks,

Data management framework (formerly known as DIXF) is a powerful architecture in D365FO which supports for all integrations. Data entities which are part of DIXF is the only way (in most of the cases) to communicate with D365FO through an external system.

Recently I come across with a requirement where we need to have more default fields than the standard fields available when opened the entity data using excel add-ins. Using the excel add-in designer you can add but it may be difficult for end users to do this always and to find the required fields to add. In this post will take you through how to add or remove fields required to make defaults fields to use in the excel-addins.

In D365fo the data can be exported to excel using the “Export to excel” option which export the data for fields available in the grid, if required export more fields data you can add more fields in the grid using the personalization and insert columns.

But when you use the “Open in excel” option which uses the data entities available for the current form which can be used to manage the CRUD operations using the excel Add-in. This is a very good tool for business users to manage the data for their day to day operations. 

Coming to the main point of the topic if say customer wanted to add more fields or remove couple of fields by default when opened in excel.

For that you have to go to visual studio, open the data entity ( if the entity is standard, create extension). Go to fields field groups àAutoReport field group. Here you can see all the fields which are already showing on the excel. Now you can add or remove or change the order of the fields you want.

Once do the build, after the successful build, refresh your browser and run the excel add-in to see the expected changes.

Here I have taken the simple example of customer group standard entity, you can see the fields are already coming in excel.

Above screen shows the existing fields information, now will do modifications as follows. Here added three new fields as shown in below and you can see the results in excel after the successful build.

When the customer group opened in excel add-in, I see the change as follows. Here we see only one new field coming up in the excel sheet even though we have added three fields.

Don’t worry, there is a reason for not coming up all the fields.  There is a field property called “Access Modifier” on the entity field, by which we can restrict it. If its public then we can see the that field and if its private you cannot see the field. Out of three fields newly added, two are private and only one is public, that’s why in the new excel there is only one new fields shown.

In case, the restricted fields you wanted to enable for excel add-in for a standard entity, then you cannot change the property directly, in such case you can duplicate the entity and can make the property change to public to show it.

I hope its helpful, will come up with another interesting topic in the next post.

Thank you 😊

 

 

D365 FO Batch Management Service stopped, failed to start and throwing error 1064

Hello Everyone,

Recently I got an issue from our team that no batch jobs are running from couple of days in one of our  Dev box. When I investigated all the configurations are in place but still no batch jobs are executing.

Later I have logged into the RDP and to check the batch service “Microsoft Dynamics 365 Unified Operations : Batch management Service” which was stopped.

Tried to start the service which gave the following error message. And when I checked the event log I didn’t get much useful information.

 

Afterwards I realized that we have enabled the maintenance mode for enabling new financial dimensions a day back. Thought that it might have been the problem for not starting the batch service.

Then disabled the maintenance mode and the Batch management service was able to start without any issue. So maintenance mode enabled causing the issue to start the batch service.

 To enable or disable maintenance mode in dev box refer here.

Thanks.

Happy Learning !

 

 

 

 

 

 

D365 FO Connector error : Could not retrieve values, Badgateway

Hello Everyone,

Recently come across with an issue with D365FO connector from an cloud deployed instance which was used to consume the Odata services from azure logic app and power app

Issue:

Recently got an issue with D365FO connector from an cloud deployed instance which was used to consume the odata services from azure logic app and power app. It was all working fine and one fine day all of a sudden the power app and the logic app stopped fetching the data from FnO and giving the error as “Could not retrieve values, Badgateway More diagnostics information:  x-ms-client-request-id is 'xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx'.

 

Root cause:

I started troubleshooting and tried to run the web instance and instance of FO is also not running with https and its only running in private. And realized certificates would have expired and checked the same in LCS and the certificates were been expired.

Solution:

I ran the Rotate certificates/Rotate Secrets using the LCS VM options, and checked for web instance which started working and the Bad gateway issue also has been resolved.

You can refer standard Microsoft documentation for the process to Rotate Secrets for cloud deployed D365FO instance.

Thanks.

Happy Learning !