As you might know MSDyn365FO’s data access layer pretty different from T-SQL. This means that if you copy a query on AX and paste it in SSMS it won’t validate in the 99% of the cases (the other 1% being a select * from table).

The differences are specially obvious in the join clauses, for example in AX’s (not) exists join which translates to T-SQL as a WHERE NOT EXISTS (SELECT statement).

To helps us when we want to check data in SQL Server we can use xRecord’s class getSQLStatement method.

If we run this code in Dynamics 365 we’ll get the SQL query in the sql variable. Note the generateonly keyword in the select statement which will allow this, without it there’s no SQL 🙂

This is the T-SQL query we get:

I’ve removed some fields to allow better reading, but you’ll always get all the fields in the SQL query whwn using the getSQLstatement method, even if you select a single field in AX. As you see we have a ? as a parameter in the where clause instead of its value. If you want to show the value you need to use forceliterals in the select statement in D365:

Then you’ll get this:

Keep in mind that if you use generateonly in a select statement you won’t have any data in your table buffer:

Get the T-SQL query from X++ 1

This means that this functionality is only for testing or debugging purposes, if you forget removing this before checking in your code… well, you can imagine.

Subscribe!

Receive an email when a new post is published

8 Comments

  1. Is there a way to get it to work with Query objects? I understand there is a method on the Query class called getSqlStatement(), but it doesn’t work from my testing.

    • Adrià Ariste Santacreu Reply

      Hi Ryan,

      I’ve never tried. Keep in mind that when doing it on a table buffer, it needs the generateOnly clause in the select, and I don’t know if that can be achieved on a Query object.

Write A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

ariste.info