Get the T-SQL query from X++

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:

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.

6 thoughts on “Get the T-SQL query from X++

    1. Hi Riyas,
      you’re right, index hint is deprecated. I was testing if the output in SQL was still working like in AX2009 and left it when pasting the code from Visual Studio. I’ve updated the post, thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

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