Axapta Interfacing Using Stored Procedure

I got some duty to help and support our AX team to make interface between 3 platform on our project that have been running and will be go up to go live stage soon.

because we need to do interfacing between 3 difference platform consist of Magento and warehousing application, so we were decided using .csv and .xls file to sending and receiving data.

below are some queries and procedures, how to export and import using.csv and .xls.

export:

declare @table_name as varchar(200);

--set @table_name = 'WMSSO'

SET @cmdstr = 'bcp "select * from ' + @db_name + '.dbo.vTest' + '" QUERYout ' + @_FileName + ' -c -w -T -U sa -P sa","-CRAW'

--print @cmdstr

create table ##XPCmdShellOutput (OutputLine varchar(1000))

-- Show the output of xp_cmdshell when the directory does not exist

Insert into ##XPCmdShellOutput 

EXEC master..xp_cmdshell @CMDstr

there are sample query to exporting, just care on selection part, here I used xp_cmdshell, there has some limitation on string that used as parameter, so we need create a view in sql server to accommodate the result that we want to export, then the view can used on string parameter on xp_cmdshell.

so many ways to exporting from AX to another application, I prefer to using business connector or at least SSIS if we just want exporting to files such as .csv or .xls. Ok as much as this, I will update to explain how to import data to axapta table using stored procedure in the next posting.. CU.