Enum values SQL – D365 finance and operation

Enum values in D365 finance and operations stored in a table ‘ENUMIDTABLE’ and ‘ENUMVALUETABLE’. Below is the query to get or verify the enum values

Select eit.NAME,evt.ENUMID,evt.ENUMVALUE, evt.NAME as EnumValueName 
from ENUMIDTABLE eit
inner join ENUMVALUETABLE evt 
on eit.ID= evt.ENUMID
where eit.NAME='TaxDirection'

QR code for the page:

Batch job could not be found. Dynamics 365 for Finance and Operations, Ax2012 R3

Scenario/Problem:

Sometimes users facing an issue in the batch jobs when they delete the batch job in executing state from the back end or for any other reason(like batch job stuck in executing stage). Then they need to perform below steps to resolve the issue.

In my  case i was facing issue in the workflow batch job and my workflow are not working even after restarting the server or service.

Solution:

 

Perform select query on the below tables with caption filter by typing the keyword of the job you are looking for.

Batch and Batchjobs

Deleted batchjob has been deleted from Batchjob table but not from Batch table. So, I performed below query and my issue has been resolved.

 

First select query on both tables and then delete query to delete the records.

 

Failure! Status check of Report Server – The remote server returned an error: (500) Internal Server Error – Report design not found – Microsoft Dynamics 365 for Finance and Operations

Scenario:

Sometimes users facing below issue in generating the reports from Dynamics 365 for Finance and operations or Developers while deploying the reports 

• Failure! Status check of Report Server –
• The remote server returned an error: (500) Internal Server Error – Report design not found
• Report design not found

Solution:

Restart the below services by going on the services list window of the report server or from the LCS

• Restart the service report server
• Check and restart the Microsoft SQL Server instance service

Note: If the server is sandbox environment managed by Microsoft then go the Power BI (report server) VM and restart the above services

 

 

 

How to check table Id in Dynamics 365 for Finance and Operations – SQL Server

Scenario: Sometimes developer needs table id of the table to use in their logic

Solution: you can get the table id for any table using sql query in the development environment.

SysTableIdView is the table from where you can fetch the Id.

Below is the query

 

select * from SysTableIDView

Sandbox UAT SQL server database access – Microsoft Dynamics 365 for Finance and Operations

Scenario: Accessing of Microsoft Dynamics 365 for Finance and Operations database on sandbox environment.

In August 2019 Microsoft did the database scaling and migration in different regions. so might be your Dynamics 365 for Finance and Operations might be changed. When you trying to logon you will face some difficulty.

Below are the steps to access the new sql server and database.

• Copy the SQL server name from LCS and put on logon screen

Example: spartan-tes-nam-d365tessst-test123456.database.windows.net

 

• After adding above information click on options

 

• Copy the database name(below screenshot before AXDB) from the LCS environment and paste in the connect to database field as per above screenshot

 

• Click on connect and now you are successfully able to connect

 

Note: Please leave comments if you are facing any issue by following the above steps.

Export Database from UAT Environment and restore in the Dev Environment – Restore the database in the local Dev VM – Microsoft Dynamics 365 for Finance and Operations

Scenario : During testing we face many problems and those problem can only find by debugging the code with the actual data. Then we need to take the backup of the UAT environment and restore it to the development environment.

Solution : Below are the steps needs to perform to take the backup and restore

Export the Database:

1. Go to LCS
2. From sandbox Environment Details page, click the Maintain menu, and then select Move database.
3. Select the Export Database option
4. Database will exported as bacpac file to the Database backup option in the Asset Library

Import the Database:

1. Copy the bacpac file to the Development enviornment
2. Import as a data tier application
3. If data tier application failed then import using below commands

4. cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin

   SqlPackage.exe /a:import /sf:D:\Exportedbacpac\uatBackup.bacpac /tsn:localhost /tdn:SSProd /p:CommandTimeout=1200

    

   • tsn (target server name) – The name of the SQL Server 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.

1. After successfully database import run below commands to update the database logins details as per current environment (at the end of the article) (No need to run this command if the current environment is local dev vm not Microsoft managed)
2. Run the retrovision tool to use the new database
3. Stop the services
   1. World wide web publishing service
   2. Batch Management service
   3. Management reporter service
4. rename the database as AxDB after stopping the service and rename the old one as AxDBOld

   1. REATE 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
      EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'
      
      CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser
      EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'
      EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'
      
      CREATE USER axdeployextuser WITH PASSWORD = '<password from LCS>'
      EXEC sp_addrolemember 'DeployExtensibilityRole', '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
      
      ALTER DATABASE [<your AX database name>] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)
      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 Catalog

Shrink Log File – Microsoft SQL Server

Scenario: Sometimes Microsoft SQL server database log file size is huge. Then, it needs to reduce to custom limit

 

Solution: Use below code to reduce the log file size to specific limit

 

USE AdventureWorks;

GO

— Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE AdventureWorks

SET RECOVERY SIMPLE; 

GO

— Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (AdventureWorks_log, 10);

GO

— Reset the database recovery model.

ALTER DATABASE AdventureWorks

SET RECOVERY FULL; 

GO