AX2012: Error when downgrading the AX server kernel

Description:

 It can happen that you need to downgrade the AOS kernel in a system. Depending on the build you are going back to, you might get an error message.

Error message:

Object Server 01:  Fatal SQL condition during
login. Error message: “The internal time zone version number stored in the
database is higher than the version supported by the kernel (4/1). Use a newer
Microsoft Dynamics AX kernel.”

Solution:

You need to change the value for the column value SYSTIMEZONESVERSION in the table SQLSystemVariables. If you look in
the event viewer you can see that it finds the value 4, but expected 1 (4/1).
So the solution is to change the value to 1.

Fixing database is in use error while restore database from backup

Method 1
declare @sql as varchar(20), @spid as int

select @spid = min(spid)  from master..sysprocesses  where dbid = db_id('<database_name>')
and spid != @@spid  

while (@spid is not null)
begin
    print 'Killing process ' + cast(@spid as varchar) + ' ...'
    set @sql = 'kill ' + cast(@spid as varchar)
    exec (@sql)

    select
        @spid = min(spid)
    from
        master..sysprocesses
    where
        dbid = db_id('<database_name>')
        and spid != @@spid
end

print 'Process completed...'


Method 2

select spid from master..sysprocesses where dbid =db_id('<database_name>') and spid <> @@spid


SELECT *
FROM sys.sysprocesses
WHERE dbid = DB_ID('<database_name>')

AX2012 AXUTIL commands to Import,Export,Delete Models

// creating the Model in the Layer
AxUtil create /model:"TestModel" /Layer:CUS

// exporting the model to file
AxUtil export /model:"TestModel" /file:TestModel.axmodel

// importing the model from file
AxUtil import /file:TestModel.axmodel

// delete the model
AxUtil delete /model:"TestModel"

//delete the layer
AxUtil delete /layer:ISV

// if you have multiple instances running
//delete the layer from the database and AXserver.
Axutil delete /layer:ISV /db:<database> /s:<server>

Model and Model Store in ax 2012

Basic Info:

Models were introduced in Microsoft Dynamics AX to help partners and customers more easily install and maintain multiple solutions side by side in the same layer. This topic introduces the concept of models, and describes how models relate to layers and label files. This topic also describes the model store, which is the part of the Microsoft Dynamics AX database in which models are stored.

Models:
A model is a set of elements in a given layer. Each layer consists of one or more models. Each layer contains one system-generated model that is specific to that layer. Every element in a layer belongs to only one model. In other words, no element can belong to two models in the same layer, and every element must belong to a model.

A model is permanently associated with the layer that is created in. If you need to move one of your models from one layer to another, you must create a project from the model in the AOT, export the project as an xpo file, create a target model in the desired layer, delete the original model to avoid having to resolve layer conflicts, and import the xpo file to the target model. If you are moving elements between models in the same layer, you can use the Move to model command in the AOT.

ModelStore:

Models are stored in the model store. The model store is the part of the Microsoft Dynamics AX database in which all application elements for Microsoft Dynamics AX are stored. Customizations are also stored in the model store. The model store replaces the Application Object Data (AOD) files that were used in earlier versions of Microsoft Dynamics AX. Models that have been installed in the model store are used at run time.

Note: Models can be exported to files that have the .axmodel extension. These files are called model files. Model files are deployment artifacts. Model files can be signed with strong name signing and Microsoft Authenticode signing.

How to manage Label files with Model:

In Microsoft Dynamics AX 2012, label files, or ALD files, are part of models. A label file must be added to a model before the model can be installed. After a model has been installed, ALD files are pulled from the model store to the local of Application Object Server (AOS) instance when the AOS is started. When the AOS is shut down, the ALD files are pushed back to the model store.

ALD files from earlier versions of Microsoft Dynamics AX are not part of a model file. However, you can import these files into the model store from the Label Files section of the Application Object Tree (AOT). Use the Create from file shortcut command.

Note: The ALD file from an earlier version of Microsoft Dynamics AX must not be located in the application folder of AOS. Otherwise, you cannot import the file.

Working with label files across solutions

It is recommend that you use one label file per solution to simplify installation.

If you find that you require multiple label files, it is recommend that you create a single shared, cross-solution label file and package it as a model file. Then, when you install solutions, you must install two models: the solution itself and the label model.

If you want to ship additional languages, you can add the languages to the solution model, increment the model’s version number, and then re-import the model.

Export an .axmodel file (Windows PowerShell)

1. On the Start menu, point to All Programs, point to Administrative Tools, and then click Microsoft Dynamics AX Management Shell.
2. At the Windows PowerShell command prompt, PS C:\>, type the following command, and then press ENTER.

   Export Command Syntax :

   Export-AXModel –Model <name> -File <Filename.axmodel>

   Example :

   Export-AXModel –Model SecurityModel -File C:\SecurityModel.axmodel

Import an .axmodel file (Windows PowerShell)

1. 1. On the Start menu, point to All Programs, point to Administrative Tools, and then click Microsoft Dynamics AX Management Shell.
   2. At the Windows PowerShell command prompt, PS C:\>, type the following command, and then press ENTER.

   Import Command Syntax :

   Install-AXModel -File <Filename.axmodel> -Details

   Exmaple :

   Install-AXModel -File C:\SecurityModel.axmodel -Details

Exporting a Model with AXUtil.exe

To export a model by using AXUtil.exe follow these steps.

1. Click Start > All Programs > Accessories and right-click Command Prompt selecting Run as administrator.
2. Change directory to “\Program Files\Microsoft DynamicsAX\60\Server\MicrosoftDynamicsAX\Bin” or to the bin directory for your server installation.
3. Type “AxUtil.exe export/file:<path to save your model>.axmodel/model:<model name>” and then press Enter to export the model.

Importing a Model with AXUtil.exe

To import a model by using AXUtil.exe follow these steps.

1. Click Start > All Programs > Accessories and right-click Command Prompt selecting Run as administrator.
2. Change directory to “\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\Bin” or to the bin directory for your server installation.
3. Type “AxUtil.exe import /file:<path to where you saved your model>.axmodel and then pressEnter to import the model.

Exporting a model store

The model store that you export remains in the original environment. The model store is not deleted.

Export a model store (Windows PowerShell)

1. On the Start menu, point to All Programs, point to Administrative Tools, and then click Microsoft Dynamics AX Management Shell.
2. At the Windows PowerShell command prompt, PS C:\>, type the following command, and then press ENTER.
   Windows PowerShell

   Export-AXModelStore -File <Filename> -Details
   

   This command exports the model store to an .axmodelstore file.
   For more information, see Export-AXModelStore.

Export a model store (AXUtil)

1. On the Start menu, click Command prompt.
2. Navigate to the directory for the management utilities. Typically, the location of this directory is %ProgramFiles%\Microsoft Dynamics AX\60\ManagementUtilities.
3. At the command prompt, type the following command, and then press ENTER.
   axutil exportstore /file:filename [/verbose]
   

   This command exports the model store to an .axmodelstore file.

Importing a model store

When you import a model store, you can import the new model store directly to the default schema, dbo. Alternatively, you can import the model store to a different schema, and then apply the model store to the default schema.

• If you import the new model store directly to the dbo schema, downtime is likely to be greater, because you must stop all Application Object Server (AOS) instances while the model store is imported.
• If you import the model store file to a non-default schema, downtime may be less, because AOS instances can remain active throughout the import process.

AOS instances must be stopped when the new model store is applied to the default schema. However, less time is required to stop an AOS instance than to import a model store.

When you import a model store, you can back up the existing model store to another schema. The schema that you back up the model store to cannot exist before you import the model store.

By default, if a conflict of element IDs occurs while an .axmodelstore file is imported, the import is stopped.

By default, optimization steps are performed if the number of elements in a model increases by more than 50 percent when a model or model store is reimported. For more information, see How to: Optimize a Model Store for Runtime.

Import a model store to the dbo schema (Windows PowerShell)

1. Drain all client connections from the AOS instance that you are working with. For more information, see Drain users from an AOS.
2. At the Windows PowerShell command prompt, PS C:\>, type the following command, and then press ENTER.
   Import-AXModelStore -File <Filename> 
   

   This command imports a model store to the Microsoft Dynamics AX database and associates the model store with the default schema, dbo. You can also use the -BackupSchema parameter to copy the model store that is overwritten to a new schema. The schema that is specified by the -BackupSchema parameter cannot exist at the time that you run the cmdlet.
   For more information, see Import-AXModelStore.

Install a model store to the dbo schema (AXUtil)

1. Drain all client connections from the AOS instance that you are working with. For more information, see Drain users from an AOS.
2. On the Start menu, click Command prompt.
3. Navigate to the directory for the management utilities. Typically, the location of this directory is %ProgramFiles%\Microsoft Dynamics AX\60\ManagementUtilities.
4. At the command prompt, type the following command, and then press ENTER.
   axutil importstore /file:filename
   

   This command imports a model store to the Microsoft Dynamics AX database and associates the model store with the default schema, dbo. You can also use the /BackupSchema parameter to copy the model store that is overwritten to a new schema. The schema that is specified by the /BackupSchema parameter cannot exist at the time that you run AXUtil.

Import a model store to a temporary schema, and then apply the model store (Windows PowerShell)

1. For this example, the non-default schema is named TemporarySchema.
   At the Windows PowerShell command prompt, PS C:\>, type the following command, and then press ENTER.
   Import-AXModelStore -File Staging.axmodelstore -SchemaName TemporarySchema
   

   This command installs the model store file in the schema that you specify.

   Note
   The model store that you import to the non-default schema is not visible to Microsoft Dynamics AX.

2. After the model store has been imported, drain all client connections from the AOS instance that you are working with. For more information, see Drain users from an AOS.
3. After all AOS connections have been closed, type the following command, and then press ENTER.
   Import-AXModelStore -Apply:TemporarySchema
   

   This command applies a model store that was associated with the non-default schema, TemporarySchema, to the dbo schema. The model store then becomes visible to Microsoft Dynamics AX.
   For more information, see Import-AXModelStore.

Install a model store to a temporary schema, and then apply the model store (AXUtil)

1. For this example, the non-default schema is named TemporarySchema.
   On the Start menu, click Command prompt.
2. Navigate to the directory for the management utilities. Typically, the location of this directory is %ProgramFiles%\Microsoft Dynamics AX\60\ManagementUtilities.
3. At the command prompt, type the following command, and then press ENTER.
   axutil importstore /file:filename /schemaname:name /verbose
   
   

   This command installs the model store file in the schema that you specify.

   Note
   The model store that you import to the non-default schema is not visible to Microsoft Dynamics AX.

4. Drain the client connections that are connected to the AOS instance that you are working with. For more information, see Drain users from an AOS.
5. At the command prompt, type the following command, and then press ENTER.
   axutil importstore /apply /schemaname:TemporarySchema /verbose 
   

   This command applies a model store that was associated with the non-default schema, TemporarySchema, to the dbo schema. The model store then becomes visible to Microsoft Dynamics AX.

How to import Contoso Demo Data to Dynamics Ax 2012 R3.

The setup contoso demo data for Dynamics Ax 2012 R3 is different from previous version. I remember, I used .dat file to load demo data into Dynamics Ax 2012 R2. Now DynamicsAX2012R3DemoData.exe available partner Resource.

DynamicsAX2012R3DemoData.exe extracted files round 15 GB. Extracted folder contains three types of files. According to MSDN

• Xml (A bcp data file that contains table data. Columns are separated by #|EOC|#. Rows are separated by #|EOR|#\n.)
• Out (A bcp data file that contains the table metadata (column descriptions).
• OutModel (This metadata includes all names and IDs of the table and its fields. This file also includes the elementType attribute, which stores the names and IDs of any Microsoft Dynamics AX tables, classes, or extended data types that are referenced by the table)

Installation of Test Data Transfer Tool:

The MSDN Described The Import export with Test Data Transfer Tool (beta) as follow.

Test Data transfer Tool is available on partner resource.

Go on following link

https://informationsource.dynamics.com//RFPServicesOnline/Rfpservicesonline.aspx?ToolDocName=Microsoft+Dynamics+AX+2012+Test+Data+Transfer+Tool+(Beta)%7cQJ4JEM76642V-8-1465

On login I found download page

There will be zip file downloaded.  On running I found following installation wizard.

Setup run will be generated as following

Import Data Into Dynamics Ax 2012 R3 instance:

And import MetaDataXMLGenerator.xpo in dynamics Ax.

This xpo imports results a job inside job node under AOT.

This job will generate Metadata.xml. This Metadata.xml will be created in window temporary folder.

Path of file in metadata.xml can be get from infobox which will appear after successful run of job.

if you lost infobox,You can get temporary folder path with following command echo %temp%. where you can get Metadata.xml

Copied metadata.xml into [list] folder of test tool Data folder. If it already exists then overwrite it.

Now open command prompt. Go to directory where Test import tool is extracted.

Run the following command

DP.exe IMPORT “E:\Contoso_Demo_Data\DynamicsAXR3DemoData” MicrosoftDynamicsAx

Please update path according to your installation.

After running the command, you will find following window which describe the process by remaining table to number data, and number of error occurs during import.

This process takes hours and hours with respect to your machine. After completion, I found demo data inside my dynamics Ax 2012 R3.

Dynamics AX 2012 R3 Demo database restore/ Contoso DB restore in Dynamics AX 2012 R3

1)      Download demo data from partner source

2)      Extract It,  at some location let’s assume our path to extract it is C:\R3DemoData

3)      As It will get extracted it will created a subfolder, under directory which is specified in step#2 i.e. C:\R3DemoData \DynamicsAXR3DemoData\

It will take almost 15 GB to expand/ extract

4)      Download Test Data transfer Tool from information source; here goes the path for it.

https://informationsource.dynamics.com//RFPServicesOnline/Rfpservicesonline.aspx?ToolDocName=Microsoft+Dynamics+AX+2012+Test+Data+Transfer+Tool+(Beta)%7cQJ4JEM76642V-8-1465

5)      Install this tool i.e. Test Data transfer Tool

6)      Open Dynamics AX and Press Ctrl+D to Open AOT. Remember if you do not have license file you would not be able to open AOT. It’s must to load license file and sync DB, before you can access AOT.

7)      Import an XPO File, named as MetadataXMLGenerator.xpo

This file can be found at the installation location of Test Data transfer Tool. Commonly under normal circumstances it would be available at following path

C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)

8)      After you import the XPO, go to Jobs, you will see a job created, in the AOT. The name of job will be MetadataXMLGenrator. Run this job and wait until you get an info message like this

“Metadata xml file is created and here is full path to filec:\UsersSMCSER\AppData\Local\Temp\MetaData.xml”

Copy the whole message of infolog and paste it somewhere in notepad etc. It’s important

9)      Stop Dynamics AOS Service.

10)   Copy a file on following path

File Name: MetaData.xml

File from: path mentioned in step#8

File to: Test Data transfer Tool installation path\List

Typically as C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)\[Lists]

11)   Open CMD, and Run as Administrator

12)   Run the following command
      DP.exe IMPORT “PATH  OF EXTRATCED DB WHICH IS DISCUSSED IN STEP 2 AND 3. USE PATH OF STEP 3; SEE EXAMPLE BELOW” MicrosoftDynamicsAx

DP.exe IMPORT “C:\R3DemoData \DynamicsAXR3DemoData\” MicrosoftDynamicsAx

Note: if you are getting connection error for database, try command as under

dp.exe import C:\R3DemoData \DynamicsAXR3DemoData\ MicrosoftDynamicsAX

13)  That’s all, it may ask you some option, as Y/Y and n/N. and it will keep on showing you status that how many tables are remaining and how many got error during import, as shown below. At this moment you may have rest or coffee until it’s done :)