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;

            ;

            try

            {

                // Executes the Query statement on the back end database.

                resultSet = SQLHelper::resultSetExecuteQuery(queryStmt);

                while(resultSet.next())

                {

                    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);

                                break;

                            case 1: // Interger

                                expectedSubResult += resultSet.getInt(i);

                                break;

                            case 2: // Real

                                expectedSubResult += resultSet.getReal(i);

                                break;

                            case 3: // Date

                                expectedSubResult += resultSet.getDate(i);

                                break;

                            case 4: // Enum

                                expectedSubResult += resultSet.getBoolean(i);

                                break;

                            case 6: // UtcDateTime

                                expectedSubResult += dateTime2str(resultSet.getDateTime(i));

                                break;

                            case 45: // Guid

                                expectedSubResult += guid2str(resultSet.getGuid(i));

                                break;

                            case 49: // Int64

                                expectedSubResult += resultSet.getInt64(i);

                                break;

                            default:

                                break;

                        }

                        // End of Switch

                    }

                    // End of for

                    expectedResult += [expectedSubResult];

                }

            info(con2Str(expectedSubResult));

                // End of while

            }

            catch

            {

                Error('error');

            }

            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);

            permission.assert();

            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");

        dialog.run();

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

        {

            str query = queryInput.value();

            container con = SQLHelper::getExpectedResultFromQuery(query);

            info(con2Str(con));

            File::SendStringAsFileToUser(con2Str(con),'sqlresult.txt');

            //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 : - 

www.abc.cloud.dynamics.com/?mi=sysclassrunner&cls=SqlBrowser

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.