| Author |
Message
|
| kirankinnu |
 Posted: Thu Nov 01, 2007 8:16 am Post subject: Calling stored procedure in SQL Server |
 |
|
Centurion
Joined: 12 Jun 2004
Posts: 128
Location: Chicago, IL
|
I am calling a stored procedure from ESQL to SQL Server. Here is the Stored procedure in SQL Server
Procedure dbo.usp_TestMQ
(
@pInput varchar(50),
@pOutPut varchar(60) output
)
Here is my ESQL
CREATE PROCEDURE usp_TestMQ(IN pInput CHAR, OUT pOutPut CHAR)
LANGUAGE DATABASE EXTERNAL NAME "dbo.usp_TestMQ";
SET pInput = 'SampleData';
CALL usp_TestMQ(pInput, pOutPut);
I am getting this error back.
"The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure."
I have read in some of the previous posts that calling SQL server stored procedures is not supported in broker. Is this true with V6?
I would appreciate If someone could point to what mistake I am doing.
Thank You,
Kiran |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Thu Nov 01, 2007 8:33 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I'm mostly guessing here, but @pInput might need to be declared as INOUT in the ESQL declaration.
It's not declared as INPUT only.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| kirankinnu |
| Posted: Thu Nov 01, 2007 10:03 am Post subject: |
|
|
Centurion
Joined: 12 Jun 2004
Posts: 128
Location: Chicago, IL
|
Thanks Jeff for the Hint
It is not the pInput but pOutPut should be defined as INOUT.
Thank You,
Kiran |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Nov 01, 2007 12:18 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Glad you got it working. For the record SQLServer stored procedures are indeed supported in Broker V6
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| kirankinnu |
| Posted: Mon Nov 05, 2007 9:07 am Post subject: |
|
|
Centurion
Joined: 12 Jun 2004
Posts: 128
Location: Chicago, IL
|
Do we have any restrictions on the parameter's size that we send and receive for Stored Procedures. We are planning to send an xml file as input to the Stored procudure and receive a long string of Characters as Output. I did some testing. It looks like I am only able to receive only 100 chars as output.
Please advice If there is a go around.
Thank You,
Kiran |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Nov 05, 2007 10:40 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
There is a limit for output params (but is is around 5mb) and even then
there is a way to increase this. In your case if you can only get 100 chars
I would open a PMR, as that does not sound right...
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| kirankinnu |
| Posted: Mon Nov 05, 2007 12:11 pm Post subject: Solved |
|
|
Centurion
Joined: 12 Jun 2004
Posts: 128
Location: Chicago, IL
|
I am not sure what the reason is. But its working now. Initially the stored procedure output parameter was defined as varchar(100) and i was receiving 100 chars. Then the DBA made a change to output parameter as varchar(500). Even then I was receiving 100 chars. I removed all the flows, re-deployed and restarted the broker. Even then I was getting the same 100 chars. I was about to open a PMR, thats when i was receiving all the expected chars. I think the broker is storing the schema somewhere.
Thank You for the support.
Kiran |
|
| Back to top |
|
|
|
|
