| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| mssql 2008 stored procedure call error wmb 8 |
« View previous topic :: View next topic » |
| Author |
Message
|
| csongebalazs |
 Posted: Thu Mar 21, 2013 3:49 am Post subject: mssql 2008 stored procedure call error wmb 8 |
 |
|
Voyager
Joined: 30 Jan 2004
Posts: 78
|
Hi,
There is an MSSQL2008 Enterprise 64bit R2 database on a windows 2008 Enterprise 64 bit R2 server SP1, and a message broker 8 runtime with fixpack1.
In a message flow there is a stored procedure call, which result the following error:
The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.
There is an another windows 2003 server (32 bit) with MSSQL 2005 server and broker 7.0.0.4, where this flow working properly. I changed ODBC definition in this old server in order to point to new MSSQL database on a new server and made a test. In this setup there were not any error.
My tip, its is an another bug in message broker 8. Can any1 confirm it?
Is it a known bug and/or is any solution to awid this problem?
Broker 7 had a same problem with MSSQL 2008 in the begining, which was fixed in the 3rd fixpack.
http://www-01.ibm.com/support/docview.wss?uid=swg1IC76897
Could it be a same issue with broker 8? |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Thu Mar 21, 2013 4:06 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
You might like to check the user context in which the SP is being called. The view from the Broker service Id may not be the same as with your own Id.
Collect MustGather documentation and open a PMR.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| csongebalazs |
| Posted: Wed Apr 03, 2013 12:37 am Post subject: |
|
|
Voyager
Joined: 30 Jan 2004
Posts: 78
|
Hi,
I opened a PMR. IBM said this issue will be handled under this APAR:
http://www-01.ibm.com/support/docview.wss?uid=swg1IC83407
After fixpaxk 2 arrival, my error message gone during sp call, but got new one:(
Database error: SQL State ''HY104''; Native Error Code '0'; Error Text ''[Microsoft][SQL Server Native Client 10.0]Invalid precision value''
I made ODBC connection to MSSQL server with "SQL Server Native Client 10.0" ODBC driver, because WMB documentation contains the following:
Define a data source for Microsoft SQL Server:
Select the driver for the version of SQL Server that you are using:
1. SQL Native Client for SQL Server 2005.
2. SQL Native Client 10.0 for SQL Server 2008.
I deleted my ODBC connection and made new one with "SQL Server" ODBC driver, and test message was processed.
Parameters on MSSQL side:
| Code: |
CREATE PROCEDURE [dbo].[IO_Log_Felvevo]
@Uzenet_CorrelId binary(24),
@Uzenet_Fajta_Azonosito char(50),
@Uzenet_ReplyToQ varchar(48),
@Uzenet_ReplyToQMgr varchar(48),
@Uzenet_Mqmd varchar(max),
@Uzenet_Body varchar(max),
@Uzenet_BeKi_Flag char(1),
@FK_Be_Log_ID int,
@MessageDomain varchar(10),
@MessageSet varchar(32),
@MessageFormat varchar(10),
@GroupId varchar(24),
@MsgCount varchar(16),
@MsgSeqNumber varchar(16),
@KommPont varchar(1024),
@KommTipus varchar(32),
@Uzenet_Fej varchar(max),
@Prioritas int,
@Uzenet_Msg_ID varchar(128),
@Idobelyeg datetime,
@RendszerElem_Azonosito varchar(3),
@DEAD_Log char(1),
@LOGID int OUTPUT
AS |
Parameters on WMB side:
| Code: |
CREATE PROCEDURE IO_Log_Felvevo (
IN Uzenet_CorrelId BLOB,
IN Uzenet_Fajta_Azonosito CHAR,
IN Uzenet_ReplyToQ CHAR,
IN Uzenet_ReplyToQMgr CHAR,
IN Uzenet_Mqmd CHAR,
IN Uzenet_Body CHAR,
IN Uzenet_BeKi_Flag CHAR,
IN FK_Be_Log_ID INT,
IN MessageDomain CHAR,
IN MessageSet CHAR,
IN MessageFormat CHAR,
IN GroupId CHAR,
IN MsgCount CHAR,
IN MsgSeqNumber CHAR,
IN KommPont CHAR,
IN KommTipus CHAR,
IN Uzenet_Fej CHAR,
IN Prioritas INT,
IN Uzenet_Msg_ID CHAR,
IN Idobelyeg TIMESTAMP,
IN RendszerElem_Azonosito CHAR,
IN DEAD_Log CHAR,
INOUT LOGID INT) LANGUAGE DATABASE EXTERNAL NAME "dbo.IO_Log_Felvevo"; |
In order to avoid a possible TIMESTAMP - DATETIME conversion problem, i removed this parameter from sp definition, but error did not disappear. Maybe VARCHAR(MAX) - CHAR not matching, or i don't know.
Any idea, why the officially required ODBC driver not orking, and other driver does? |
|
| Back to top |
|
|
| bielesibub |
| Posted: Mon Jun 10, 2013 5:08 am Post subject: |
|
|
Apprentice
Joined: 02 Jul 2008
Posts: 40
Location: Hampshire, UK
|
csongebalazs, you might be right with your varchar(max) comment.
We're moving from v7.0.0.5 32-bit WMB, SQL Server 2008, Windows 2003 to 8.0.0.2 64-bit WMB, SQL Server 2008 R2, Windows 2008 R2 64-bit and have the same problem as you. I am not suggesting this as a fix, but I've found changing the stored procedure defs from varchar(max) to varchar(8000) has removed the error message.
Additionally, we were getting a right truncation error from one of our procedures, again, the varchar(max) was changed to a varchar(8000) and the problem went away.
I want to be clear, THIS IS NOT A RECOMMENDATION FOR A FIX! Just a pointer to what *might* be causing the problem.
Bielesibub |
|
| Back to top |
|
|
| csongebalazs |
| Posted: Mon Jun 10, 2013 5:52 am Post subject: |
|
|
Voyager
Joined: 30 Jan 2004
Posts: 78
|
The problem still under investigation by the IBM LVL3 support.
They made an APAR fix for it, but that did not worked for me (and with that fix the SQL Server ODBC driver failed too). After the fix was applied, I did not got any precision value error, but at random cases got 2 types of exceptions. Most exceptions were String data, right truncation but found an Attempt to create a duplicate schema exception too.
So the fix made worse situation, because it eliminated my backdoor oportunity (using SQL Server ODBC driver instead of SQL Server Native Client 10.0 ODBC driver).
Maybe varchar(8000) can be a good solution some cases, but in my case I have to log message bodies in char form (not in blob), and must be able to log longer message bodies than 8K.
Using PASSTRHU command can be the other workaround, but till SQL Server ODBC driver is working, I not want change the application code. |
|
| Back to top |
|
|
| csongebalazs |
| Posted: Mon Aug 12, 2013 12:26 am Post subject: |
|
|
Voyager
Joined: 30 Jan 2004
Posts: 78
|
The story was continued. They made an another fix which eliminated the String data, right truncation errors, but not the Attempt to create a duplicate schema errors.
Meanwhile I figured out, the duplicate schema error can occur during a flow start if there is any message in the input queue.
They sent me a fix for this problem too, but the case was not closed, because the following reason.
Both fixes for different problems requires a same "install" method, what means I have to replace files under the broker's bin directory. The fix for the stored procedure call errors are necessary in order to test anything else, because the message flow terminates at the first logging step without it. But both fixes require to replace same files, and duplicate schema fix doesn't contain other fix, so cannot test that. 
The ball is on the IBM side again. |
|
| Back to top |
|
|
| smuktineni |
| Posted: Wed Aug 28, 2013 10:55 am Post subject: |
|
|
Apprentice
Joined: 28 Aug 2003
Posts: 33
Location: Omaha
|
In WMB SQL procedure signature, try changing all the MSSQL paramters defined as varchar(max) to BLOB instead of CHAR.
Ex:
| Code: |
| @Uzenet_Mqmd varchar(max), |
would be represented
| Code: |
| IN Uzenet_Mqmd BLOB, |
FYI... we are at Broker V8.0.0.2
_________________
-Satish
Last edited by smuktineni on Thu Aug 29, 2013 4:45 am; edited 1 time in total |
|
| Back to top |
|
|
| csongebalazs |
| Posted: Thu Aug 29, 2013 12:36 am Post subject: |
|
|
Voyager
Joined: 30 Jan 2004
Posts: 78
|
An extra detail. Using the fix for invalid precision value problem, main message processing was fine, but if I passed NULL values in the stored procedure parameters, I got BIP2329E: An unexpected NULL value was encountered. exceptions. After some tests I figured out, those parameters are problematic which MSSQL side type is VARCHAR(MAX). Not a big supprise...
In broker 8 there is a way to define parameter as NULLABLE in CREATE PROCEDURE, but it did not help. If I passed 'n/a' string in these parameters all was fine, and I was able to pass NULL values in fields with other types.
But today, the story seems to be finished. IBM made a new combined fix, which cures invalid precision value and duplicated schema problems in one. And with this fix there is not any problem with NULL value handling either.  So after some months finally they were able to hit 3 flies with 1 strike.  |
|
| Back to top |
|
|
| smuktineni |
| Posted: Thu Aug 29, 2013 5:06 am Post subject: |
|
|
Apprentice
Joined: 28 Aug 2003
Posts: 33
Location: Omaha
|
Did you try changing ESQL signature to BLOB?
Speaking to our DBA, MSSQL treats VARCHAR(max) as BLOB vs VARCHAR(nnnn) is treated as variable character length.
_________________
-Satish |
|
| Back to top |
|
|
| csongebalazs |
| Posted: Thu Aug 29, 2013 5:18 am Post subject: |
|
|
Voyager
Joined: 30 Jan 2004
Posts: 78
|
| No, I did not try. Ty for the tip, but they fixed what they made wrong, so now no need to found a workaround. But if I have some free time I will test that too. |
|
| Back to top |
|
|
| vani_cool |
| Posted: Wed Oct 09, 2013 4:31 pm Post subject: |
|
|
Novice
Joined: 15 Jul 2009
Posts: 17
|
| Did u tried this with the Stored procedures which returns CURSOR? any one ? |
|
| Back to top |
|
|
| vani_cool |
| Posted: Wed Oct 09, 2013 4:32 pm Post subject: |
|
|
Novice
Joined: 15 Jul 2009
Posts: 17
|
We are using Broker 8.0.0.2 with SQL SERVER 2012 and when we call the Stored procedure which returns CURSOR, it generates the same error
The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.
any helps or pointers? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Oct 10, 2013 3:04 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| vani_cool wrote: |
We are using Broker 8.0.0.2 with SQL SERVER 2012 and when we call the Stored procedure which returns CURSOR, it generates the same error
The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.
any helps or pointers? |
Have you followed the advice above? Did you contact your DBA ? Did you verify in WMB documentation that ESQL supports MS SQLServer cursors ?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| csongebalazs |
| Posted: Mon Nov 25, 2013 5:05 am Post subject: |
|
|
Voyager
Joined: 30 Jan 2004
Posts: 78
|
I am so sad, because have to make a new reply here. I thought this case was closed when IBM made the combined fix for me (as I wrote 29th Aug), but I was wrong.
Nowdays I installed the runtime fixpack 3, which (by the paper) contains fixes for those problems, what I reported them before. At first everythink was fine, but when I tried to call a stored procedure from broker and pass a NULL value for the nullable VARCHAR(MAX) field, I got the well known An unexpected NULL value was encountered. exception AGAIN!!! 
After 8 months.... |
|
| 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
|
|
|
|
