MQSeries.net :: View topic - Issue calling SQL Server Stored Procedure

fundoo · Posted: Mon Nov 22, 2010 5:17 am Post subject:

Hi,

Getting an exception while calling a SQL server stored procedure. Using MB v7.

Following is the stored proc defn in esql:
CREATE PROCEDURE getPolicyDetails(IN POLICYREFNO CHARACTER)
LANGUAGE DATABASE DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.test";

Following is the call in esql:
CALL getPolicyDetails(POLICYREFNO, Environment.ResultSet1[]);

Following is the sql server stored proc in sql server db:
CREATE PROCEDURE dbo.test(
@REQNO VARCHAR(100)
)
AS
set nocount on
begin

BEGIN

SELECT *, (SELECT L1.DESCRIPTION FROM CCDB.dbo.ABCTable L1 WHERE L1.TYPEFOR='COVERAGEOPTION' AND L1.VALUE=M.COVERAGEOPTION) AS COVERAGEDESC
FROM CCDB.dbo.XYZTable M
WHERE M.H_CALLIDNO=@REQNO
END
if @@ERROR <> 0
Return(-1)
else
Return
end

Folowing is the exception thrown when executing the esql call statement listed above. Thanks in advance for any help....

ExceptionList
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbDataFlowNode.cpp
Line:INTEGER:1073
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmMQInputNode
Name:CHARACTER:FGPolicyDetails#FCMComposite_1_1
Label:CHARACTER:FGPolicyDetails.MQ Input
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbComputeNode.cpp
Line:INTEGER:489
Function:CHARACTER:ImbComputeNode::evaluate
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:FGPolicyDetails#FCMComposite_1_3
Label:CHARACTER:FGPolicyDetails.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp
Line:INTEGER:641
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:FGPolicyDetails#FCMComposite_1_3
Label:CHARACTER:FGPolicyDetails.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.FGPolicyDetails_Compute.Main
Insert
Type:INTEGER:5
Text:CHARACTER:4.4
Insert
Type:INTEGER:5
Text:CHARACTER:CopyEntireMessage();
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlRoutine.cpp
Line:INTEGER:628
Function:CHARACTER:SqlRoutine::invoke
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:FGPolicyDetails#FCMComposite_1_3
Label:CHARACTER:FGPolicyDetails.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2934
Text:CHARACTER:Error occured in procedure
Insert
Type:INTEGER:5
Text:CHARACTER:CopyEntireMessage
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp
Line:INTEGER:641
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:FGPolicyDetails#FCMComposite_1_3
Label:CHARACTER:FGPolicyDetails.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.FGPolicyDetails_Compute.CopyEntireMessage
Insert
Type:INTEGER:5
Text:CHARACTER:8.3
Insert
Type:INTEGER:5
Text:CHARACTER:getPolicyDetails(POLICYREFNO, OutputRoot.XMLNS.Data[]);
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlRoutine.cpp
Line:INTEGER:756
Function:CHARACTER:SqlRoutine::invoke
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:FGPolicyDetails#FCMComposite_1_3
Label:CHARACTER:FGPolicyDetails.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2934
Text:CHARACTER:Error occured in procedure
Insert
Type:INTEGER:5
Text:CHARACTER:getPolicyDetails
DatabaseException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbOdbc.cpp
Line:INTEGER:341
Function:CHARACTER:ImbOdbcHandle::checkRcInner
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2321
Text:CHARACTER:Root SQL exception
Insert
Type:INTEGER:2
Text:CHARACTER:-1
Insert
Type:INTEGER:14
Text:CHARACTER:libbipodbc.a(odbc.so)
DatabaseException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbOdbc.cpp
Line:INTEGER:480
Function:CHARACTER:ImbOdbcHandle::checkRcInner
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2322
Text:CHARACTER:Child SQL exception
Insert
Type:INTEGER:5
Text:CHARACTER:21000
Insert
Type:INTEGER:2
Text:CHARACTER:512
Insert
Type:INTEGER:5
Text:CHARACTER:[IBM][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.