| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Stored Procedure in MS SQL Sever |
« View previous topic :: View next topic » |
| Author |
Message
|
| RichA |
 Posted: Mon Jan 26, 2004 8:03 am Post subject: Stored Procedure in MS SQL Sever |
 |
|
Centurion
Joined: 14 Mar 2002
Posts: 102
|
I've been having trouble calling a stored procedure in SQL server, Ive tried using a passthru such as -
SET OutputRoot.Result[] = PASSTHRU('EXEC/CALL inputvalidate ?,?,?', InputRoot.A, InputRoot.B, InputRoot.C)
This fails because A,B & C are long character variables (100+K) and using SQL profiler it appears only the first 16 characters of each variable are getting passed.
I've also tried -
DECLARE err CHARACTER;
CALL inputvalidate(InputBody.BatchId);
CREATE PROCEDURE inputvalidate ( IN batchID INT, OUT err CHAR ) EXTERNAL NAME "inputvalidate" ;
This fails because -
DatabaseException BIP2324E: Unsupported datatype '-9' fetched from database table column 'PROCEDURE_CAT'.
Which method should I be using, will either work properly?
I have just noticed the SQL Profiler is reporting the call as -
exec sp_stored_procedures N'dreaminputvalidate', N'dbo', NULL
despite the fact InputBody.BatchId is populated in this case with the integer 3.  |
|
| Back to top |
|
 |
| mgk |
| Posted: Tue Jan 27, 2004 3:26 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
We do not support CREATE PROCEDURE with SQLServer, so that is why that is not working.
We also do not support OUT parameters with stored procedures called via PASSTHRU, so make sure you are not doing that. We also do not support the returning of a single scalar value in any form of Stored Procedure (stored function really) so ensure this is not the case.
If you are only passing IN parameters to your SP, try it with something simple (lile hard-coded small strings < 16 chars) to verify that is is being called properly. Then (assuming that you can call it ok) increase the string length untill you hit your problem.
Somethings to think about:
*Does your string have embedded NULLs?
*What datatype are the procedure's parameters defined to be at the SQLServer end (CHAR, CLOB, VARCHAR etc?)
*Look at an ODBC trace to see what the call we make to SQLDescribeParam and SQLBindParameters are returning, and taking, to make sure they are sensible.
If all this fails, you should raise a PMR.
Cheers,
_________________
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 |
|
|
| RichA |
| Posted: Wed Jan 28, 2004 2:01 pm Post subject: |
|
|
Centurion
Joined: 14 Mar 2002
Posts: 102
|
Thanks, I was trying to use both of these unsupported techniques at various times, I did try to call the stored procedure with hard coded strings > 16 characters, which from what the SQL profiler was reporting to me was only receiving the first 16 characters, I'm not sure if this is something to do with what the SQL profiler telling me not being the whole truth or not as I'm no SQL Server expert, it's just how it appears to me.
To resolve the problem I'm now calling the stored procedure with an integer and putting the results into a table for the flow to pick up afterwards. This at first glance appears to work.
At least now I know that these methods are not supported and it's not something I can do anything about makes me feel a bit better. |
|
| Back to top |
|
|
| MQ_LEO |
| Posted: Thu Jan 04, 2007 3:25 pm Post subject: WMB V6.0 supports SQL Stored Procedure |
|
|
Apprentice
Joined: 06 Jul 2006
Posts: 29
|
Hai every body.
My code is
CALL SCHEDULED_HEARING(Environment.ResultSet1[]);
CREATE PROCEDURE SCHEDULED_HEARING()
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.SCHEDULED_HEARING";
but it not givving any data from database. please let me know WMB V6.0 supports SQL stored procedures.or not
My database ---- Microsoft SQLServer.
Thanks,
MQ_LEO |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
