Někdy je nutné přistupovat ke container datům v AX z uložené procedury přimo v T-SQL. Bohužel, není možné pro tento přístup využít Business Connector, proto se musíme spokojit pouze s COM connectorem. Z tohoto důvodu nelze tento přistup využít v AX 2009, protože ten již COM connector nepodporuje.

Jak tedy přistoupit ke container datům v T-SQL? Použijte následující kód jako příklad:

USE DynamicsAX40_1;
GO

DECLARE @_hResult int;
DECLARE @_ax int;
DECLARE @_axRecord int;
DECLARE @_recFound bit;

-- Create an instance of AxaptaCOMConnector.Axapta3 class
EXEC @_hResult = sp_OACreate 'AxaptaCOMConnector.Axapta3', @_ax OUTPUT;
IF @_hResult <> 0
	GOTO handleAxError;
	
-- Log on to AX
EXEC @_hResult = sp_OAMethod @_ax, 'LogonAs', NULL, 'userName', 'domain', 'proxyAccountName', 'proxyAccountDomain', 'proxyAccountPassword', 'company', 'language', 'objectServer', 'configuration';
IF @_hResult <> 0
	GOTO handleAxError;

-- Create instance of IAxaptaRecord
EXEC @_hResult = sp_OAMethod @_ax, 'CreateRecord', @_axRecord OUTPUT, 'SalesTableDelete';
IF @_hResult <> 0
	GOTO handleAxError;

-- Execute statement on Ax Business Logic
EXEC @_hResult = sp_OAMethod @_axRecord, 'ExecuteStmt', NULL, 'SELECT * FROM %1';
IF @_hResult <> 0
	GOTO handleAxError;

-- Read value of property Found to check if there are some records returned
EXEC @_hResult = sp_OAGetProperty @_axRecord, 'Found', @_recFound OUTPUT;
IF @_hResult <> 0
	GOTO handleAxRecordError;

-- Iterate through the records in the table to get detailed information
WHILE @_recFound = 1
BEGIN
	DECLARE @_recLabel varchar(200);
	
	EXEC @_hResult = sp_OAGetProperty @_axRecord, 'Caption', @_recLabel OUTPUT;
	IF @_hResult <> 0
		GOTO handleAxRecordError;
	
	PRINT @_recLabel;
	
	-- Get the container - BEGIN
	
	DECLARE @_container int;
	DECLARE @_containerRecord int;
	DECLARE @_containerRecordCaption varchar(200);
	
	EXEC @_hResult = sp_OAGetProperty @_axRecord, 'Field', @_container OUTPUT, 'SalesTable';
	IF @_hResult <> 0
		GOTO handleAxRecordError;
	
	EXEC @_hResult = sp_OAMethod @_container, 'Peek', @_containerRecord OUTPUT, '1';
	IF @_hResult <> 0
		EXEC sp_OAGetErrorInfo @_container;
	
	EXEC @_hResult = sp_OAGetProperty @_containerRecord, 'Caption', @_containerRecordCaption OUTPUT;
	IF @_hResult <> 0
		EXEC sp_OAGetErrorInfo @_containerRecord;
	ELSE
		PRINT N'SalesTable column inhalt caption: ' + @_containerRecordCaption;
		
	-- Clean up
	EXEC sp_OADestroy @_container;
	EXEC sp_OADestroy @_containerRecord;
		
	-- Get the container - END
	
	EXEC @_hResult = sp_OAMethod @_axRecord, 'Next';
	IF @_hResult <> 0
		GOTO handleAxRecordError;
	
	EXEC @_hResult = sp_OAGetProperty @_axRecord, 'Found', @_recFound OUTPUT;
	IF @_hResult <> 0
		GOTO handleAxRecordError;
END;

GOTO quit;

handleAxError:
EXEC sp_OAGetErrorInfo @_ax;
GOTO quit;

handleAxRecordError:
EXEC sp_OAGetErrorInfo @_axRecord;
GOTO quit;

quit:
-- Log off from Ax
EXEC sp_OAMeThod @_ax, 'Logoff';

-- Don't forget to release all objects created
EXEC sp_OADestroy @_axRecord;
EXEC sp_OADestroy @_ax;
GO