14.09.2017, 13:11 | #1 |
Участник
|
domhk: Use PowerShell and Business Connector to retrieve data from Dynamics AX (AX2009)
Источник: http://domhk.blogspot.com/2015/11/us...connector.html
============== Not long ago I started a new position as an in-house AX specialist. Part of my job is to provide support to users on their AX issues. Here I face a challenge that, due to organization structure, I don't have direct access to the SQL database. If I want to quickly check some data in AX I am then left with the table browser. Another option is to write X++ jobs but then I'll have to take care of showing the data (e.g. using infolog) myself. In short, it's not ideal. I missed the ability to write ad-hoc scripts and the ability to conveniently re-run them (Used to be T-SQL scripts for me). Then I thought I can use PowerShell! I tried to look up the internet for PS scripts to access AX and saw this. Unfortunately, I couldn't get it to work. Well, time to write my own. =D Here I connect to AX using the business connector. First I have to get the Microsoft.Dynamics.BusinessConnectorNet.dll and put it somewhere. Load it up and create an Axapta object from it. $targetPath = "C:\Temp\Microsoft.Dynamics.BusinessConnectorNet.dll" [reflection.Assembly]::Loadfile($targetPath) | Out-Null $ax = new-object Microsoft.Dynamics.BusinessConnectorNet.Axapta Then I connect to AX and retrieve data. $company = "" $language = "" $aos = "" $config = "" $ax.logon($company,$language,$aos,$config) $t1 = $ax.CreateAxaptaRecord("SalesTable") $t2 = $ax.CreateAxaptaRecord("SalesLine") $ax.ExecuteStmt("SELECT * FROM %1 JOIN %2 WHERE %1.SalesId == %2.SalesId && %1.SalesId == 'SO-100004'",$t1,$t2) Do { $t1.get_field("salesId") + " - " + ` $t2.get_field("itemId") + "; " + ` $t2.get_Field("SalesQty") + "; " + ` $t2.get_Field("LineAmount") } while ($t1.Next()) The scripts above returns below when run against the demo database. SO-100004 - 1151; 2; 121.38 SO-100004 - 1151; 2; 124.42 SO-100004 - 1161; 5; 197.95 SO-100004 - 1161; 7; 333.83 SO-100004 - 1401; 1; 204.39 SO-100004 - 1402; 1; 257.53 SO-100004 - 1606; 2; 800 What's written above works but it just run a very specific query. I'll share a basic script module I put together in my next post. It'll have better reusability. This posting is provided "AS IS" with no warranties, and confers no rights. Источник: http://domhk.blogspot.com/2015/11/us...connector.html |
|
|
|