Sunday, January 10, 2021

How to access data via SQL Commands in Production Environment in D365 FO

Accessing the data for Production Environment is one of the major challenges which consultants face especially when there is a data discrepancy issue and because of this issue major financial statements are getting impacted.

Microsoft has given us very limited privileges when it comes to Sandbox environments and that too can be done only in UAT Environments. For Production all the control is there with Microsoft Product Team only. So the Programmers have somehow found a way to run basic SQL scripts in Production Environment and perform some data manipulations.

Note : - Data Manipulation can always be proved risky so before deploying this customization to Production Environment we have to proceed with caution and always keep the Production DB copy in our LCS Asset Library

Here is how we can do it in few simple steps : - 

1) Open Visual Studio as Administrator in your Tier 1 or Tier 2 DEV Environments :

2) Create New Project and associate it with your own custom model

3) We need to create 2 classes : - SqlBrowser and SqlHelper

4) First let's begin with the class creation for Sql Browser

5) Right click on Project -- >  Add New Item --> Class --> SQLHelper

6) Use the code mentioned below : - 

class SQLHelper

        public static Container getExpectedResultFromQuery(str queryStmt)
            int i;
            int colType;
            container expectedSubResult, expectedResult;
            int counter;
            ResultSet resultSet;

                // Executes the Query statement on the back end database.
                resultSet = SQLHelper::resultSetExecuteQuery(queryStmt);

                    expectedSubResult = connull();

                    for ( i = 1; i <= resultSet.getMetaData().getColumnCount(); i++)
                        colType = resultSet.getMetaData().getColumnType(i);
                        switch (colType)
                            case 0: // String
                            case 8: // Memo
                                expectedSubResult += resultSet.getString(i);
                            case 1: // Interger
                                expectedSubResult += resultSet.getInt(i);
                            case 2: // Real
                                expectedSubResult += resultSet.getReal(i);
                            case 3: // Date
                                expectedSubResult += resultSet.getDate(i);
                            case 4: // Enum
                                expectedSubResult += resultSet.getBoolean(i);
                            case 6: // UtcDateTime
                                expectedSubResult += dateTime2str(resultSet.getDateTime(i));
                            case 45: // Guid
                                expectedSubResult += guid2str(resultSet.getGuid(i));
                            case 49: // Int64
                                expectedSubResult += resultSet.getInt64(i);
                        // End of Switch
                    // End of for

                    expectedResult += [expectedSubResult];
                // End of while

            return expectedResult;



        private server static ResultSet resultSetExecuteQuery(str strQuery)
            Connection  connection = new Connection();
            Statement   statement = connection.createStatement();
            SqlStatementExecutePermission permission;
            ResultSet resultSet;

            // Get the formatted Query statement.
            strQuery = strfmt(strQuery,SRSStatementQuery::getDbSchemaPrefix());
            permission = new SqlStatementExecutePermission(strQuery);

            resultSet = statement.executeQuery(strQuery);

            return resultSet;

 7)  Now Create SQLBrowser class which will call the functionality of SQLHelper

class SqlBrowser


    public static void Main(Args _args)


        Dialog dialog= new Dialog("SQL");

        DialogField queryInput = Dialog.addField(extendedTypeStr(MyExtendedDataType),"SQL query");;

        if(dialog.closedOk() && queryInput.value())


            str query = queryInput.value();

            container con = SQLHelper::getExpectedResultFromQuery(query);



            //exporting result of select statement to txt file



8) Now create Action Menu Item for the class Sql Browser

9) If you do not want to create a menu item then you will have to access it through the browser url as mentioned below : -

10) Once your menu item or class is called it will show you the screen like below : - 


11) That's it enter your query and you will get your desired result.


  1. Hi - nice article.

    Does there exist any kind of guide or documentation to accessing common Dynamics elements - for example querying a list of Products, Contacts, Quotes or Orders?


  2. Hi ,

    There are different tables for different masters in D365 FO.

    FOr e,g For Products - The main table is inventtable

    For Contacts - The maintable is ContactPerson

    For Sales Quotation - The main table is SalesQuotationTable (Header) , SalesQuotationLines (Lines)

    For Sales Order - The Main table is SalesTable (Header) , SalesLine (Lines)

    Do let me know what exactly you are looking for.

  3. You can find support or guidance from individuals who know your technology precisely and possibly have experienced this issue previously and figured out how to investigate it. IT consulting services Brampton

  4. a SQL infusion assault can happen. By sending unforeseen information, an assailant can produce and submit SQL questions to a web applications data set.


How to reverse Free Text Invoice Voucher entries without Dialog

 Hey Folks ,  This blog post is in continuation of the previous post for Reversing Free Text Invoice Voucher entries with Dialog. Only diffe...