AX 2012 - SQL Server Collation

There is a difference in collation setting between SQL Server default collation setting used for system databases (Master, Model, Msdb, TempDB) and the Microsoft Dynamics AX database . Latin1_General_CI_AS_KS_WS  v.s. Latin1_General_CI_AS

 

Database Name	Collation name
AX2012PROD	Latin1_General_CI_AS
master	SQL_Latin1_General_CP1_CI_AS
model	SQL_Latin1_General_CP1_CI_AS
msdb	SQL_Latin1_General_CP1_CI_AS
tempdb	SQL_Latin1_General_CP1_CI_AS

It is recommended that the collations of user-defined databases match the collation of master or model. Otherwise, collation conflicts can occur that might prevent code from executing. For example, when a stored procedure joins one table to a temporary table, SQL Server might end the batch and return a collation conflict error if the collations of the user-defined database and the model database are different. This occurs because temporary tables are created in TempDB, which bases its collation on that of model.

 

If you experience collation conflict errors, consider one of the following solutions:

·        Export the data from the user database and import it into new tables that have the same collation as the master and model databases.

·        Rebuild the system databases to use a collation that matches the user database collation. For more information about how to rebuild the system databases, see Rebuilding System Databases (http://technet.microsoft.com/en-us/library/dd207003.aspx).

 

More information can be found here:

http://technet.microsoft.com/en-us/library/bb402915.aspx
Windows Collation Name (Transact-SQL)


Here you can find a good post that explain the difference between collation Latin1_General_CI_AS (United Kingdom) respect SQL_Latin1_General_CP1_CI_AS (United States).
It’s better to use Latin1_General_CI_AS collation because is a Windows collation and the rules around sorting unicode and non-unicode data are the same.

Difference between collation SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS

 

AX 2012 - Developer Resources

AX 2012 Developer Resources


 

Raid Disk space utility

Hi

Interesting utility for calculate a Disk space according with the Raid type.

Raid Disk space utility

 

AX 2012 - Client crashe while posting intercompany invoice

Microsoft Dynamics AX 2012 client crashes while posting intercompany invoice


 

SSRS Report AX 2012 - The operation has timed out error message when you run a report in Microsoft Dynamics AX

Hi


About this problem, read below and test to find if this will have improved SSRS Report performance:

There is a process to change long running jobs so that they are run in a Pre-Processing way, so that all the data is prepared before the SSRS Report Window is started. This prevents the timeout problem, sometimes shown by the message ““A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond”

To change the report to run in these pre-processing way ( Similar Sales confirmation, Sales Invoice, etc. reports ), see this example below for the Dimension Statement report on how to change this:

 

1.    To find which object you need to modify, first look in the AOT > Menus, for the Menu where the report is

 

2.    View the properties on this to see the associated menu item. You can see below the menu item is “LedgerDimensionTransStatement”.

3.    Find this menu item in AOT > Menu Items > Output

…and look at the properties, make a note of the “LinkedPermissionObject”, in this case “LedgerTransStatement”

4.    Next in the AOT > SSRS Reports > Reports, locate LedgerTransStatement, then expand this out until you see the Server Methods. Make a note of the Server Method class, in this case “LedgerTransStatementDP”

5.    In the AOT > Classes, locate and open class LedgerTransStatementDP.

6.    In the LedgerTransStatementDP\classDeclaration, change line 9 to extend SrsReportDataProviderPreProcess instead of SrsReportDataProviderBase

7.    Make a note of the Temp table used in the report, as above this is LedgerTransStatementTmp.

8.    Next, change the method LedgerTransStatementDP\processReport to add the following line after the contract (line 27):

 

ledgerTransStatementTmp.setConnection(this.parmUserConnection());

 

9.    Next, in AOT > Data Dictionary > Tables, locate the table you made a note of in point 7, so in this case the LedgerTransStatementTmp. Change the table properties as follows:

 

·         TableType = Regular

·         CreatedBy = Yes

·         CreatedTransactionId = Yes

 

10. Opened LedgerTransStatement.Detail report in Visual Studio and refreshed the data source to include new field (CreatedTransactionId).

11. Deployed the new LedgerTransStatement.Detail report.

12. In AX, did a Generate Incremental CIL.

13. Restart SSRS

Also, at this link

Microsoft Dynamics AX 2012 Reporting: How to run reports that executes longer than 10 minutes

The operation has timed out" error message when you run a report in Microsoft Dynamics AX 2012

AX 2012: Report timeout error

How To: Addressing SSRS Session Timeouts

you can find useful information about modify the SQL Reporting Send Timeout Parameter.

Denis



 

Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages could not be found

Hi All

If you have the follow error :

"The type 'Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages, Microsoft.SharePoint.Portal, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' could not be found"
during Enterprise portal navigation, try to delete the entrance in your web.config of EP application.

See also http://insomniacgeek.com/how-to-fix-the-the-type-microsoft-sharepoint-portal-analytics-ui-reportviewermessages-does-not-implement-ireportviewermessages-or-could-not-be-found-error/

Enjoy !
 

Upgrade Dynamics AX 4.0/2009 to AX 2012 R2 with AX Rehost Solution

Hi ALL

For the Customers with many Dynamics AX personalizations, upgrade process could take long time and to be very expensive.

For this reason, we have create a .NET Solution that move the Entire AX 4.0/2009 Application ( Entire AOT ) to AX 2012 R2.

Customer scenario and needs

- Customer is using AX4/AX2009
- AX4 SP2 exited mainstream support since 11/10/2011
- Current AX4/AX2009 implementation are complex
- Many customizations and interfaces
- AX4/AX2009 implementation are multi country
- Customer wants/needs upgrade to AX 2012 R2 (instead of buying AX4/AX2009 extended support)
- «Big bang» upgrade to AX 2012 approach is unrealistic
- Customer needs a quick and cost effective solution to decouple the Technical upgrade from the Application upgrade
   - To adopt a «big bang» approach for the Technical upgrade
   - To adopt a «phased» approach for the Application upgrade
   - Upgrade companies in separate steps. Customize Intercompany between AX 2012 to AX 4/2009 and viceversa.

Value proposition

- Decoupling the Technical upgrade from the Application upgrade
- Quick and cost effective AX4/AX2009 rehosting solution using :
    - AX4/AX2009 Application
    - AX 2012 R2 Kernel
- Ability to upgrade the application in phases
- Ability to use AX 2012 R2 technical features
      64bit Kernel, Support for SQL 2012, Windows Server 2012, Windows 8, SQL Reporting, SQL Analysis, Workflow, WEB Services, Excel Add-In, etc.

AX4/2009Rehost – Technical details

AX4/2009 Rehost solution is sold as a service and uses a series of proprietary .NET tools that execute the following functions:
      - Export AX4/2009 AOT objects (all layers including SYS) and rename objects using a prefix
      - Conversion of AX4/2009 AOT objects syntax to AX 2012 R2 and remap the objects to use the new AX 2012 kernel interfaces
      - Import of the converted AX4/2009 AOT objects into AX 2012 R2 in the VAR Layer (Model AX Rehost)
      - Import of AX4/2009 Data tables with specific conversions (i.e. TableId and FiledId)
      - Manual steps must be performed to redefine the menu structure and to test the new environment



Enjoy !

 

Office Add-in work also with a non Admin AX user

Hi all

If an AX user haven't Sys Admin Role and don't use an AIF service, can't read and publish data with Office Add-in

The workaround is :

1- Add privilege AIFGenericDocumentService to the Role Systemuser. This permit the access to the class AIFGenericDocumentService

2- Add the table AifGdsCache to the permission of the Role Systemuser. This table is necessary for the cache using by Add-in

3- Add to the user the role Systemuser and the roles related the specific area


With this workaround you can't add a table to Excel, but you can prepare a Excel file with an AX Admin user and share with other non Admin AX users.

Enjoy !

AX 2012 - Resource Page for Upgrade AX 4.0\2009 to AX 2012

Hi

Below many interesting topics.

Resource Page for Upgrading to Microsoft Dynamics AX 2012

Upgrading to Microsoft Dynamics AX 2009

Microsoft Dynamics AX 2012 White Papers: Code Upgrade

White Paper on Common Upgrade Performance Tips

Common Performance Optimizations for Upgrade Code White Paper for Microsoft Dynamics AX 2012

Important Links for Upgrading to Microsoft Dynamics AX 2012

 How to: Perform in-place upgrade to Microsoft Dynamics AX 2012 R2 [AX 2012]

AX Content: AX 2009 Fields that Moved in AX 2012

 Enjoy!