Have you ever encountered such an error while doing a routine database backup;

RESTORE failed for Server
Restore failed for Server 'SERVERNAME'.  (Microsoft.SqlServer.SmoExtended) ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: RESTORE cannot process database 'YourDataBaseName' because it is in use by this session. It is recommended that the master database be used when performing this operation. (Microsoft.SqlServer.Smo)
The first thing to come in mind perhaps would be to ... end all active connections to the database. Some of us would straight go to the detach database option to see the active connections. What ? 'Ctrl + Alt + A' you said for activity monitor to end the processes? Yes of course but the above error won't go even if you restart the whole SQL Server service. Why ? What could be wrong ?

The mystery in our case was the fact that for some reasons unknown, the default database was changed from master to the Dynamics AX transaction database. Now when ever you try to restore on a database that is the default database of the active user, this message would appear.


Make sure the database you are restoring is not your user's default database :)

FRx 6.7 installation and configuration on Dynamics AX 2009

FRx is a very good tool for financial reporting. Now obsolete perhaps, since MR ->Management Reporter for DAX 2009 and above has taken its place. A casual installation checklist is as follows;
  • Install FRx 6.7
  • Run scripts in the correct order [file names suggestible]
  • Install the latest service packs [see the links section below]
  • Import the 2 jobs
    • frx.xpo [gives AOT -> Jobs -> DumpVirtualCompanyMap]
    • frxmetadata.xpo [gives  AOT -> Jobs -> FRxMetaData]
  • Execute the jobs
    • AOT -> Jobs -> DumpVirtualCompanyMap - save the xml file
    • AOT -> Jobs -> FRxMetaData - save the xml file
  • Run the Financial Dimension wizard
    • In the FRx installation directory, run the file named 'Microsoft.FRx.Integrations.DimensionWizard.
  • Create a new system DNS and connec it to your AX db [you can set both Windows Auth or SQL Server Auth]
  • Start FRx report designer
  • Provide keys for your company and register the software. Without this step, FRx wont work fully.
  • Set Company information - Provide the newly created system DSN in the 'Data Source Name' field.
  • Set the company as default
NOTE: The order of the checklist might not be absolutely perfect, however this order would surely end you with a working FRx application :)

Common issues and resolutions

In the last 2 points above, you may get some errors while setting your company as default or just selecting your company to start working. You may get one of the following errors;
  • Direct Link error: Login Failed. Make sure the Login ID and password are correct and verify the settings on the system specific information page in company. FRX32.OFSIMain.CheckOFSIConnection 8900: A Direct Link error has occurred attempting to connect to the DB for this company. [src: FRx DirectLink error]
  • The specified company could not be found: FRX32.OFSIMain.CheckOFSI Connection 8900: The specified company could not be found. 
Both of these issues alternatively have been messing my FRx experience for quite some time. And then I realised to go for the SQL Profiler in order to dig deep. This revealed very critical information that was quite helpful in resolving the issues. 

Their are two key tables identified in the resolution of these issues;

  • frl_sys_options: Holds each company configured in Dynamics AX
  • frl_entity: Holsd the company specific information provided from FRx client 
These are created and filled by the scripts that are ran during installation. Both of these tables are queried when saving company information at FRx or setting the company as default. Setting your company as default is necessary for FRx to work on your company. 

If the scripts are not executed at all, or missed due to negligence or any other reason, the table would either not be created or not filled. In both cases, this would cause errors. This table should have a record for each company configured in AX. The dimension wizard is also dependant on this table and would not load entities [companies] correctly. See the image below;

Financial Dimension wizard
FRx installation: Dimension wizard not showing any company due to data missing in the system table

Similarly an empty 'frl_sys_options' table would give the DirectLink error. DirectLink is also given for other issues like wrong password etc. Verify the scripts are perfectly executed. If not, execute them in the correct order. 

The other table, 'frl_entity' holds the company data that you set from the FRx configuration window. However if while setting the company information and system specific information, you do not provide the dimension structure field, it would cause the Specified company could not be found error.
FRx - System specific information
FRx -> System specific Information. Note the Dimension structure field is mandatory hence bordered.

From the investigation, I came to know that each company is queried based on its dimension structure. So consider this field as MANDATORY and DO provide this field while setting the company information.

Where is this Dimension Structure configured in AX ? or What to write in the Dimension structure field?

This is not configured by you or anyone else from the AX client. The scripts fill the table with Company code [DAT for instance] and the string part '-Default'. So you can provide 'YourCompanyCode-Default' [DAT-Default for instance] or you can simple query the table to see what is it expecting you to write.

Unfortunately I could not found any detailed investigative blog when I was facing these issues and I was misdirected a lot towards issues at the ODBC datasource and authentication level. Now after these investigations, FRx installation remains no issue for me at all. Links are at your service below :)

Some useful FRx 6.7 Links

Country Region codes in Dynamics AX 2012 R2

maqk® by MolviDSLR Khan | 11:51 AM

The hierarchy of License Codes and Configuration Keys have one more far related cousin to mention here, The Country Region Code. I have just figured out this relation due to the nature of both being the same, Enable / Disable functionality. 

License Codes toggle features. Configuration Keys toggle various functionalities inside the boundaries of those features. Country / Region Codes toggle what? The answer to this is nothing from out of the box but neither is my topic to discuss here. 

Country / Region Codes can perhaps toggle both features and functionalities inside, but based on what ? is my topic. Again its simple so lets keep it simple. A picture worth a million words, or sometimes even more than that :)

Country / Region Codes - Applicable at both Table and Field levels

The question is how do they apply in AX 2012 ? Previously to toggle country region specific functionality, country region configuration keys were provided. In R2, those keys are still there, but they don't toggle any thing, because regional functionality toggle is now seperately handled at country region codes. This CountryRegionCode attribute is applicable on tables, fields, EDTs, BaseEnums, Forms (fields and controls), Menus, Menu Items etc

From the above image, it is clear that we have a couple of country region code specific fields to apply a particular region code to an item. That means each item can have a list of country / region code on which it will be available. Now how to apply a country / region code to the running system ?  And which country / region code is currently active ? The answer to these very basic questions is the country location of the primary address of the currently active company. Let my quote Microsoft here :)

Features that are specific to a country or region are enabled and disabled based on the primary address of the legal entity. For more information, see Create or modify a legal entity. [src]
The country/region that you select for the primary address of the legal entity controls the country/region-specific features that are available for the legal entity. [src] 

Apart from all this, one more thing that you should know is that security keys are obsolete in AX 2012 R2. Click here for more.


Applying Country Specific Functionality [AX 2012]

Today I woke up [spawned at office ;) ] only to find this

Dynamics AX 2012 R2 - Server terminated unexpectedly with 90 exitcode
Dynamics AX 2012 R2 - Server terminated unexpectedly with 90 exitcode

The AOS server service was down with the above error. This was not a great start for the day and ultimately I had to put myself in the investigation gear. Peeping in to the event log gave this;

Windows event log - Server terminated unexpectedly with 90 exitcode

Google returned irrelevant things so I just went to the AX Server configuration to find this

Dynamics AX 2012 R2 - Server Configuration (Note no database server provided)

Providing the database server and restarting the service resolved the issue. Now what remains is the fact that who peeped into my office yesterday (while i was at client) and did all the mess ;)