February 17, 2010 at 5:40 pm
Using SQL Server 2005 with IBM Data Access driver IBMDASQL to AS/400 DB2.
TPS is our linked server definition. It works well with EXEC('DB2 SQL Statement') AT TPS
ie:
EXEC('SELECT * FROM LIBRARY.FPCUSMAS WHERE CUSNMR = ?', @CustomerId) AT TPS
'rpc out' is enabled and using DTS with XA transactions enabled.
DECLARE @CustomerIdINT
DECLARE @ItemQtyPairsVARCHAR(8000)
SET @CustomerId = 260482
SET @ItemQtyPairs = N'750271000975026100097502710009'
EXECUTE('CALL DOUG.GETITEMUNITPRICE(?,?)', @CustomerId, @ItemQtyPairs) AT TPS
The code above returns the message "(0 row(s) affected)" in SSMS.
I am expecting data back. But I do not see any data.
If I run
CALL DOUG.GETITEMUNITPRICE(260482,'750271000975026100097502710009')
in iSeries Navigator SQL Script window, I get the expected records.
Has anyone else experienced this issue? If so, were you able to find a fix for this?
This is the SQL stored procedure that I wrote. This should work, but I am not seeing the
result set.
/*\
EXEC dbo.usp_Item_GetItemUnitPrice 260482, '750271000975026100097502710009'
\*/
ALTER PROCEDURE [dbo].[usp_Item_GetItemUnitPrice]
@CustomerIdINT,
@ItemQtyPairsVARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ResultTABLE (
ItemIdCHAR(6) NOT NULL,
QuantityCHAR(4) NOT NULL,
UnitPriceDECIMAL(7,2) NOT NULL
)
INSERT INTO @Result
EXECUTE('CALL DOUG.GETITEMUNITPRICE(?,?)', @CustomerId, @ItemQtyPairs) AT TPS;
SELECT * FROM @Result
END
The AS400 Stored Procedure is created using the statement below and
it is just a wrapper for an RPGLE program that returns an array to the
stored procedure which becomes a result set.
-- Version: V5R4M0 060210
-- Standards Option: DB2 UDB iSeries
CREATE PROCEDURE DOUG.GETITEMUNITPRICE (
IN CUST DECIMAL(6, 0) ,
IN DATA CHAR(8000) )
DYNAMIC RESULT SETS 1
LANGUAGE RPGLE
SPECIFIC DOUG.GETITEMUNITPRICE
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'DOUG/ORR001A'
PARAMETER STYLE SQL ;
I am running out of ideas and help on this would be greatly appreciated.
-sandor
February 19, 2010 at 7:22 am
IBM technical support provided me with the solution.
Need to put curly braces around the stored procedure call for the IBMDASQL driver to return the result set.
Like this:
EXECUTE('{CALL DOUG.GETITEMUNITPRICE(?,?)}', @CustomerId, @ItemQtyPairs) AT TPS
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply