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 – 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