Use import and export wizard to import records from backup database

Accidently two table records are deleted. So, we planned to restore the records from the backup database. We can export the records in CSV file and import the records by using X++. But, we need the records with same RecId.

In SQL, we have import and export tool to copy the records from source to destination tables. Before import the records, we deleted all records from the source tables to avoid duplicate record creation.

So, we have imported the records from the old database backup within 2 minutes. It is not recommended to insert the records directly in the AX database. But we don’t have any other option to fix this issue ASAP.

Please refer into the below link to understand that how to use import and export tool.

https://www.sqlshack.com/techniques-to-bulk-copy-import-and-export-in-sql-server/

Restore AX database backup in same instance with different name

We have a daily AX production database backup up plan. One day we faced a problem in the production. Some of the records are deleted. So, we planned to analyse with latest backup.

We got the below error message, when we try to restore the recent backup file with different database name.

Error message:

“The backup set holds a backup of a database other than the existing 'Test3' database.

RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)”

Solution:

I found the solution on the post https://blog.sqlauthority.com/2007/09/27/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database/

Steps:

1. Move the production database into offline mode. The database name is MicrosoftDynamicsAX.
2. Copy and paste the mdf and ldf files in different location
3. Move the production database into online mode
4. Delete the MicrosoftDynamicsAX database
5. Restore the recent database backup file with different database name (MicrosoftDynamicsAX_Backup), mention the different filename for mdf and ldf files and check the “Overwrite the existing database” option as per the post https://blog.sqlauthority.com/2013/11/23/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database-ssms/
6. Once restored successfully, we can attach the production database MicrosoftDynamicsAX
7. This is only for the initial time, after that we can restore the database into MicrosoftDynamicsAX_Backup database anytime.

Cannot create a record in user information (sysuserinfo)

We faced some issue on the development server. It will take some days to resolve the issue. So, we planned to use UAT server as development server for temporarily. The AX version is AX 2012 R3 CU11. We don’t have Visual Studio 2013 in the test server. So I downloaded and installed the Visual Studio 2013 professional edition and installed Visual Studio development tools.

I got the error message “cannot create a record in user information (sysuserinfo)” after the Visual Studio development tools installation. I googled and found the discussion on this URL https://community.dynamics.com/ax/f/33/t/246540

As per the community, post I tried to delete the AUC file and KTI files. It worked fine.

What is AUC file?

The AUC stands for Application Unicode Cache. It is a cache file. Cache file is mostly used to improve the application performance. But if the cache object is older than our server objects, then we may face some issues like the code work fine in server/developer machine but, other user will get error. The xpo move might be one of the reasons for this issue.

In my case, I just installed the Visual Studio development tools, not done anything in the model store. So, I believe AUC cache files are not only supporting to coding objects, some of the cache is supporting to run the AX application.

Optional condition in where clause

We mostly used to write select statement with where clause. Sometime, we may need to handle optional condition in the where clause. If the variable having value then we need to fetch only that record otherwise, we need to fetch all records. In this case we need to use optional condition in where clause.

For example, in the report, if user selected the customer account number in the report dialog box, then need to display the selected customer. If the user is not selected any customer account then we need to display all customers in the report.

Sometimes, we will write the condition like below

If(_accountNum == “”)

{

   While select * from custTable

   {

   }

}

Else

{

   While select * from custTable

   where custTable.AccountNum == _accountNum

   {

   }

}

But we can optimize the code like below

While select * from custTable

where (custTable.AccountNum == _accountNum || _accountNum == “”)

{

}

It might be old but, I believe it will be helpful to someone J