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 – D365FO – Request for the permission of type ‘SqlStatementExecutePermission’ failed.

While running one Job , where I was trying to execute one SQL statement in AX, I got the below stack trace : Request for the permission of type 'SqlStatementExecutePermission' failed.(S)\Classes\SqlStatementExecutePermission\demand(S)\Classes\Statement\executeQuery(C)\Jobs\test - line 21 So what is the solution for this , Very simple , we need to run this job on Server , So … Continue reading AX – D365FO – Request for the permission of type ‘SqlStatementExecutePermission’ failed.

AX – D365FO – Executing a direct SQL statement

This recipe will demonstrate how to execute direct SQL statements. We will connect to the current Dynamics AX database directly using an additional connection and will retrieve the list of vendor accounts. How to do it... Open AOT, and create a new class called vendTableSql with the following code: class VendTableSql { } server static void main(Args … Continue reading AX – D365FO – Executing a direct SQL statement

AX – D365FO – Update a field value of a table with the value of a joined table in update_recordset

This example shows that the update_recordset statement supports the joining of several tables. Data from the joined tables can be used to assign values to fields in the table that is being updated. static void Join22aJob(Args _args) { TableEmployee tabEmpl; TableDepartment tabDept; TableProject tabProj; ; update_recordset tabEmpl setting currentStatusDescription = tabDept.DeptName + ", " + … Continue reading AX – D365FO – Update a field value of a table with the value of a joined table in update_recordset