MQSeries.net :: View topic - ESQL invoke a stored procedure in SQLServer

Fede_patane · Posted: Tue Jan 30, 2007 7:54 am Post subject:

Dear Gurus,
I am trying to invoke a stored procedure in a SQL server using an ODBC source.

I am using WMB 6.0.0.1 over AIX 5.3.
The DB is SQLServer 2000 running on windows 2003 server.

The name of the ODBC source is CDP and to do this i have writen the following code into a compute node:

CREATE PROCEDURE mySP(
IN p1 INTEGER,
IN p2 INTEGER,
IN p3 CHAR,
In p4 INTEGER,
IN p5 INTEGER, IN p6 CHAR)
--LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "stp_EAI_Retrieve_VentasPOS";

CREATE COMPUTE MODULE Test_HTTP_Compute1
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN

set OutputRoot.Properties.MessageFormat = 'XML';

call mySP(8194872, 1, 'E103', 0 , 0 , '' , OutputRoot.XML.Test[]) ;
RETURN TRUE;
END;

When i execute this i get the following error:

Fault detail: BIP2230E: Error detected whilst processing a message in node 'Test_HTTP.Compute1'. : /build/S600_P/src/DataFlowEngine/ImbComputeNode.cpp: 464: ImbComputeNode::evaluate: ComIbmComputeNode: Test_HTTP#FCMComposite_1_5 BIP2488E: ('.Test_HTTP_Compute1.Main', '9.3') Error detected whilst executing the SQL statement ''mySP(8194872, 1, 'E103', 0, 0, '', OutputRoot.XML.Test[ ]);''. : /build/S600_P/src/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp: 589: SqlStatementGroup::execute: ComIbmComputeNode: Test_HTTP#FCMComposite_1_5 BIP2934E: Error detected whilst executing the function or procedure ''mySP''. : /build/S600_P/src/DataFlowEngine/ImbRdl/ImbRdlRoutine.cpp: 685: SqlRoutine::invoke: ComIbmComputeNode: Test_HTTP#FCMComposite_1_5 BIP2920E: Whilst attempting to obtain a procedure definition, the database reported that the procedure ''CDP.eai.stp_EAI_Retrieve_VentasPOS'' does not exist, or cannot be accesed. : /build/S600_P/src/DataFlowEngine/ImbDatabaseManager.cpp: 1960: ImbDatabaseManager::getDBProcedureParameterInformation: ComIbmDatabaseConnectionManager: ComIbmDatabaseConnectionManager

Please note that the part in bold should point the problem. What i suspect is that for some reason it is doing something wrong. The name of the user to conect to the DB is "eai" and i don't know why it is added automatically there.

any help would be apreciated.

Thanx

Federico
_________________
You ask why are we slaves, but I ask why are you not? What hideous force in the universe could set you free and forever taken away your peace of mind.