Monday, September 26, 2011

Code to refresh system tables in AX 2009 - SysClientSession Table

The following code will refresh the system tables in AX 2009. I had an issue with the SysClientSession table this morning and I found some documentation about from Microsoft where they suggest running a script to allow the syscronozation of systables.

The kernel uses the SYSTABVERSION parameter value in the SQL SystemVariables table to synchronize its list with the DataBase

The following were the errors I got:

1- The database reported (session 1 (-AOS-)): [Microsoft][SQL Native Client][SQL Server]Invalid object name 'SYSCLIENTSESSIONS'.. The SQL statement was: "SELECT A.SESSIONID,A.SERVERID,A.VERSION,A.LOGINDATETIME,A.LOGINDATETIMETZID,A.STATUS,A.USERID,A.SID,A.USERLANGUAGE,A.HELPLANGUAGE,A.CLIENTTYPE,A.SESSIONTYPE,A.CLIENTCOMPUTER,A.RECVERSION,A.RECID FROM SYSCLIENTSESSIONS A WHERE (SERVERID=?)"

2- Internal Error occurred executing stored procedure when creating session for the AOS.

Please backup your DB before running this script.

update SqlSystemVariables set value = value - 1 where parm = 'systabversion'

Once you do this please restart your AOS and the problem should be fixed.

Wednesday, September 14, 2011

AX 2009 Data Import / Export

The following post is about shows how to:
1-   Export data from a company in AX using a definition Group
2-   Creating a new company that will take the new data
3-   Importing the data exported from step 1 into the new company created in step 2 using a definition group as well.
In a current project, I needed to freeze the Test1 environment due to some focus testing of a certain functionality we implemented. At the same time, we needed to allow the uses to keep testing other customizations without affecting Test1.
In addition, to make the user’s lives easier, we decided to have the same data that they were using in Test1 in our Test2 instance. So, we exported the data from our Test1 environment, and then imported it to a new company in our Test2 environment.
The following steps will show this process step by step:

1-   First select the company you want to export the data from as the AX current company by clicking in company name in the right lower corner of your AX interface.

2-   This would open the “Select Company” form as shown;
3-   Select  Administration (module)
4-   Under periodic->Data exports /import ->Click Export to
5-   The “Export options” form will appear
6-   Select a definition group. You can either create a new definition group to include what you need, or just choose ALL to get all the tables.

NOTE: In some cases, the already defined “ALL” definition group will not have all the tables, so in this case you would have to create a new definition group. In my case I created a new one name “ALL_NewDef” that included the latest tables.

7-   Enter a file name and select where to save it
8-   Click OK. At this point the company date export process should be done (after processing of course), and the files (.dat & .def) should have be saved on your computer.

Now, let’s create a new company

9-   Make sure that the selected company empty and click area node Administration
10- Click company Accounts under Common forms
11-   Press (Ctrl + N) in order to Create new company
12-   Make sure you select the newly created company as the current company

Import company file already exported in the prior steps and click Administration


13- Under periodic->Data exports /import ->Click import; The “Import options” screen will appear
14- Select the “import definition group” drop down list and choose ALL (otherwise create your own)
15-   Select the .dat file (exported before) and Click Ok to start the process

The import process may take some time according to the size of transactions in the original (exported) company.
In addition, you can choose to delete all the transaction data before the import when you are importing a .dat file o an existing company account to update the data.