How to access data via SQL Commands in Production Environment in D365 FO
Views (493)
Like
Share
ReportAccessing 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: // Stringcase 8: // MemoexpectedSubResult += resultSet.getString(i);break;case 1: // IntergerexpectedSubResult += resultSet.getInt(i);break;case 2: // RealexpectedSubResult += resultSet.getReal(i);break;case 3: // DateexpectedSubResult += resultSet.getDate(i);break;case 4: // EnumexpectedSubResult += resultSet.getBoolean(i);break;case 6: // UtcDateTimeexpectedSubResult += dateTime2str(resultSet.getDateTime(i));break;case 45: // GuidexpectedSubResult += guid2str(resultSet.getGuid(i));break;case 49: // Int64expectedSubResult += resultSet.getInt64(i);break;default:break;}// End of Switch}// End of forexpectedResult += [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.
This was originally posted here.
*This post is locked for comments