With AX you have the ability to execute stored procedures directly from X++ code The benefit is greater speed in query execution and flexibility in building complex queries. See this post to learn more about the topic : https://dynamicsaxgyan.wordpress.com/2020/03/06/create-and-execute-stored-procedures-in-d365-fo-x/ I recently encountered this error in a procedure that executes a SQL stored procedure : "Connection … Continue reading AX / D365FO – Error : “Connection is busy with results for another hstmt” while executing a stored procedure
Category: SQL Statements
AX / D365FO – Summarized values / Aggregation fields in a Form with QueryRun Object
Do you want to use QueryRun object but not able to show summarized values (like SUM, COUNT, etc) as a result? Look at this article, it will give all the answers https://dev.goshoom.net/2015/11/summarized-values-in-ax-form/
AX / D365FO – How to find relation between Invoice and Packingslip
Are you trying to find the link between customer invoice and delivery note? This SQL query will show you how select b.PACKINGSLIPID packingSlip, c.invoiceId invoiceid, QUANTITY from CUSTINVOICEPACKINGSLIPQUANTITYMATCH a join CUSTPACKINGSLIPTRANS b on a.PACKINGSLIPSOURCEDOCUMENTLINE = b.SOURCEDOCUMENTLINE join CUSTINVOICETRANS c on a.INVOICESOURCEDOCUMENTLINE = c.SOURCEDOCUMENTLINE
SQL SERVER – Create an Alert for TEMPDB Growing out of control
The system database TEMPDB can appear to have a life of its own. The primary purpose of this database is to temporarily store data for specific processes before being committed to a database. When a Tempdb grows out-of-control it can be a daunting task trying to figure out what is causing the growth. This can … Continue reading SQL SERVER – Create an Alert for TEMPDB Growing out of control
AX / D365FO – How TO find a text inside a stored procedure – SQL SERVER
To search for stored procedures containing a specific text just launch this select statement SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%MyText%' AND ROUTINE_TYPE='PROCEDURE' or better you can try this SELECT [Scehma] = schema_name(o.schema_id), o.Name, o.type FROM sys.sql_modules m INNER JOIN sys.objects o ON o.object_id = m.object_id WHERE m.definition like '%MyText%' This will be … Continue reading AX / D365FO – How TO find a text inside a stored procedure – SQL SERVER
AX / D365FO – Get packing slip number and delivery date of a sales line in SQL
This SQL statement gets Packing slip number and date of a sales line select sl.salesid, sl.LINENUM, PACKINGSLIPID,DELIVERYDATE from salesline sl join inventtransorigin ito on sl.INVENTTRANSID = ito.INVENTTRANSID join CUSTPACKINGSLIPTRANS cps on cps.inventtransId = sl.inventtransid and cps.dataareaid = sl.DATAAREAID and cps.partition = sl.partition where sl.salesid = 'xxxxxxx' You'll get this result
AX – D365FO – How to count affected/updated rows in a Update_recordset statement
How to know how many rows have been updated in a Update_recordset statement? It's simple, you can just use rowCount() method of the table after the update statement like shown below update_recordset inventTable setting XXXCommercialGroupId = comunanzaXXX.SubGroupId join comXXX where inventTable.ItemId == comXXX.comunanzaCode && comXXX.SubGroupId != ''; info(strFmt("Copy Items from comXXX Subgroup Id to InventTable … Continue reading AX – D365FO – How to count affected/updated rows in a Update_recordset statement
AX – D365FO – Select Statement with an Outer Join
static void OuterJoinSelectJob3(Args _args) { SalesOrder recSalesOrder; SalesOrderLine recSalesOrderLine; struct struct4; ; struct4 = new struct ("int SalesOrderID;" + "date DateAdded;" + "str SalesOrderLineID;" + "int Quantity" ); while SELECT from recSalesOrder OUTER JOIN recSalesOrderLine WHERE recSalesOrder.SalesOrderID == recSalesOrderLine.SalesOrderID && recSalesOrderLine.Quantity == 66 { struct4.value("SalesOrderID", recSalesOrder.SalesOrderID); struct4.value("DateAdded", recSalesOrder.DateAdded); struct4.value("SalesOrderLineID", recSalesOrderLine.SalesOrderLineID); struct4.value("Quantity", recSalesOrderLine.Quantity); info(struct4.toString()); } }
AX – D365FO – Using update_recordset with CrossCompany statement
If you have tried to use Update_recordset with CrossCompany option you'll probably have faced this error : "Cannot update multiple records in yourTable(yourTable).Cross company update_recordset operation should skip database logging." The reason is because you have to disable database logging before to proceed To solve this issue you must use these methods : yourTable.skipDataMethods(true); yourTable.skipDatabaseLog(true); … Continue reading AX – D365FO – Using update_recordset with CrossCompany statement
AX – D365FO – Join multiple tables on a Insert_recordset statement
The following X++ code example shows a join of three tables on an insert_recordset statement that has a sub-select. Also, a while select statement with a similar join is shown. A variable is used to supply the inserted value for one column. The str variable must be declared with a length that is less than or … Continue reading AX – D365FO – Join multiple tables on a Insert_recordset statement
AX / Dynamics 365 For Finance and Operations blog 