Setup Bring Your Own Database (BYOD) for Dynamics 365 Finance & Operations- How to set up & Configure BYOD in D365 F&O Set up configuration to Azure SQL DB
Category Archives: SQL
D365 FO : SQL Connection & Access of production environment Using X++
- Introduction – In earlier version of Microsoft Dynamics AX, Customer and partner had full control and access of Microsoft SQL Server and they were able to connect to Microsoft SQL Server of production server.Customer and partner were also able to use SSMS (SQL Server Management Server) in Microsoft Dynamics AX Production for querying and executing direct SQL Statements in certain scenarios.
- But In Microsoft Dynamics 365 finance and operation Cloud & SaaS (Software as a service application) model , you cannot connect to Microsoft SQL server of production and you don’t have any access of production server SQL.
- Only Microsoft has full control of production servers and responsible for tasks like maintenance, backups etc.
- Customer is using LCS (Life cycle services) for deployments, service upgrades etc.
- It is not recommend to execute direct SQL statement in SQL server of production but still in certain scenarios we need it.
- Steps to connect SQL server of Microsoft Dynamics finance and operations production environment and executing SQL statements directly using X++
- In this example, login to visual studio and create a runnable class in Microsoft D365 finance & operations.
- Write the below code, for SQL update and delete command execution in production server SQL database.
- Above piece of code works just fine when you are executing SQL update or delete statements or commands. But in some scenarios, we have to use SQL select commands or statements directly in production SQL.
- In below screenshot and code i am showing how can we execute direct SQL statement and command.
- The difference bewtween update/delete & select sql statement is that in select i am using method statement.executeQuery(sqlStr) whereas in update/delete SQL statement i am using statement.executeUpdate(sqlStr).
- In this way we can execute SQL update, delete, select etc statements and commands in the production SQL database of Microsoft dynamics 365 finance and operations using X++.
- Also read my post on D365/AX7:Connect To An External SQL Database Using X++.
D365/AX7: Computed Column/Field In View Using Method & SQL Statements
Requirement
Adding a Computed Column/Field In View Using Method & SQL Statements.
Sample Code
- Add a new method “Computed” in your view.You can fully utilize SQL case statements, SQL Functions, SQL direct statements as per your requirements.
- Add a new string computed column or field in your view.
- Change the property of computed column and select your method name in “View Method” property.
- Build and Synchronize the project.Your view is now ready to use.
- You SQL View should look like this.
Cheers …Piyush Adhikari
D365/AX7:Data Entity Staging Tables Clean Using Direct SQL(Fastest Way)
Requirement & Issue
Data Entity Staging Tables clean up is one of the most important maintenance activities.The amount of data stored in a database has a great impact on its performance.Huge and unnecessary data can lead to slowness & decrease performance of the D365 Finance and operation.
There is no standard functionality & fast way to clean unnecessary data & records from Data Entity Staging Tables Using Direct SQL.
Sample Program
Below program is fastest way to truncate all the Data Entity Staging Tables in one go using direct SQL.
**** you can modify the below program as per your need***
[code language=”cpp”]
class ACXDataStagingCleanUpTableClass
{
public void operation()
{
DMFEntity DMFEntity;
RecId oldRecId;
try
{
while select * from DMFEntity
order by RecId asc
where DMFEntity.RecId > oldRecId
{
oldRecId = DMFEntity.RecId;
if (DMFEntity.EntityTable like ‘*Staging’)
this.cleanUp(Global::tableName2Id(DMFEntity.EntityTable));
}
}
catch
{
retry;
}
}
public void cleanUp(int _tableName)
{
Connection connection = new Connection();
Statement statement;
int resultSet;
int rowCount;
str sql = strFmt(‘TRUNCATE TABLE %1;’, new DictTable(_tableName).name(DbBackend::Sql));
statement = connection.createStatement();
new SqlStatementExecutePermission(sql).assert();
resultSet = statement.executeUpdate(sql);
CodeAccessPermission::revertAssert();
statement.close();
}
}
[/code]
MS SQL Server:(NOWAIT) Vs (NOLOCK) Hints
Purpose:
The purpose of this post is to explain the basic difference between two hints i.e.(NOWAIT) Vs (NOLOCK) in microsoft SQL server.
SELECT * FROM INVENTTABLEDWH WITH (NOLOCK)
SELECT * FROM INVENTTABLEDWH WITH (NOWAIT)
(NOLOCK) hint will able to read the data even if transactions are locked.Transactions can be locked due to any update or delete query.Select query with (NOLOCK) hint can return invalid or dirty data as it will fetch records even if transactions are locked.
(NOWAIT) hint will able to read the data even if transactions are locked but return you an error.
D365/AX7/SQL:ColumnStore VS Index Simple-Talk
Purpose & Explanation
In older version of Microsoft Dynamics AX , there is no option to use concept of ‘Column Store’.But in Microsoft Dynamics 365 Finance & Operations you can set the “IndexType” property of a index in a table.There are 2 values available – 1) Index 2)ColumnStore.
A columnstore index stores data in a column-wise or in columnar format where as index stores data in a row -wise format.
Columnstore index is to reduce I/O, which can have a direct impact on query performance as comparison to index.
Columnstore is designed to speed up data-warehouse queries & read-Only operations.
Columnstore is better for OLAP whereas index is better for OLTP.
