Search code examples
sqlsql-serverviewaxaptadynamics-ax-2012

Saving an SQL Server view to the Dynamics AX AOT

I have found there are significant advantages to writing views in SQL Server Management Studio rather than creating them in the AOT. However, it appears that

  • Views created in SQL Server are not accessible from X++ code and do not appear in the AOT.
  • Views created in the AOT and then modified in SQL Server Management studio can be accessed from X++ code but are overwritten when the AOT view is recompiled or synchronized.

Is there any way around this problem? Ideally SQL views could be reengineered to be saved in the AOT, but this doesn't appear to be possible.

Solution

  • We had a requirement for view that could not be designed in AOT.

    Our workaround approach:

    1. design view in AOT with fields only (computed columns with appropriate EDTs) - just add any table (main one)
    2. create static server view method with SQL DDL (from SSMS) and execute that with Statement.executeQuery
    3. call this static method at DB Synchonize (Application.dbSynchronize)