| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| BIP2322E : SQL State ''42000''; Native Error Code '8016' |
« View previous topic :: View next topic » |
| Author |
Message
|
| Archu |
 Posted: Tue Sep 06, 2011 8:23 pm Post subject: BIP2322E : SQL State ''42000''; Native Error Code '8016' |
 |
|
Novice
Joined: 17 Jun 2011
Posts: 16
|
Hi,
I am trying to invoke the SQL server stored procedure from WMB.
Versions :
OS : Unix
WMB : 7.0.0.1
SQL server : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1765.0 (X64) Feb 2 2011 17:33:22 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
Sometimes, I am getting below issue which is captured using trace.
Error:
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2011-09-06 10:03:55.253452 12 DatabaseException BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager ''libbipodbc.so''.
The message broker encountered an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database concerning this error.
Use the following messages to determine the cause of the error. Typical problems are an incorrect datasource or table names. Correct either the database or message broker configuration.
2011-09-06 10:03:55.253456 12 DatabaseException BIP2322E: Database error: SQL State ''42000''; Native Error Code '8016'; Error Text ''[IBM][ODBC SQL Server Driver][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 (""): Data type 0x63 has an invalid data length or metadata length.''.
Options tried to resolve this issue:
In my stored procedure, I have an output parameter ErrorMessage Varchar(8000).
I noticied in many posts, that the reason for this issue the Output Parameter with the length 8000. I changed it to 2000, restarted broker & tried, It worked well. But this issue is coming very regularly even after changing the length of ErrorMessage parameter to 2000.
Can anyone help me out in solving this issue? Please do the needful. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Tue Sep 06, 2011 10:41 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Have you enabled the ODBC trace yet? This would be my next step.
In my experience SQL Server is very pernickerty wrt to the data it receives.
An ODBC trace will help you pic down exactly where in the communication between the client and the server things are going wrong.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Tue Sep 06, 2011 10:59 pm Post subject: Re: BIP2322E : SQL State ''42000''; Native Error Code '8016' |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| Archu wrote: |
DatabaseException BIP2322E: Database error: SQL State ''42000''; Native Error Code '8016'; Error Text ''[IBM][ODBC SQL Server Driver][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 (""): Data type 0x63 has an invalid data length or metadata length.''.
... In my stored procedure, I have an output parameter ErrorMessage Varchar(8000). |
Which character set does SQL Server use for the column definition? The ODBC driver limits which datatypes it can use for Unicode. |
|
| Back to top |
|
|
| Archu |
| Posted: Wed Sep 07, 2011 2:27 am Post subject: |
|
|
Novice
Joined: 17 Jun 2011
Posts: 16
|
Hi,
How to find the character set does SQL Server use for the column definition? Sorry to ask such questions. Please help me out.
Also I tried taking the odbc trace. But chaning the odbc.ini file, I need to restart the broker. After restarting it works fine sometimes. I am not able to provide odbc tarce at the moment but will keep trying |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Thu Sep 08, 2011 12:53 am Post subject: Re: BIP2322E : SQL State ''42000''; Native Error Code '8016' |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| Archu wrote: |
| How to find the character set does SQL Server use for the column definition? Sorry to ask such questions. Please help me out. |
This is probably not the best place to look for Microsoft SQL Server experts. A quick Google search turned up this.
Without seeing the ESQL external procedure declaration, call, stored procedure definition, any other code, or the relevant bits of an odbc trace, there's not much more help to offer. |
|
| Back to top |
|
|
| Archu |
| Posted: Thu Oct 06, 2011 10:14 pm Post subject: Not very sure if this issue is because of SQL server alone |
|
|
Novice
Joined: 17 Jun 2011
Posts: 16
|
We are using Passthru statement to invoke the sql server stored procedure.
Problem here is , it is not failing always. same message works sometimes & when keep trying fails withe below exception. There is no connectivity issues also.
Below is the Stored procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[test_SP]
@Style1 varchar(14)
, @TypeProduct_Cd varchar(04)
, @Season_Cd varchar(04)
, @Gender_Cd varchar(01)
, @Theme_Cd varchar(10)
, @Royalty_Cd varchar(02)
, @Vendor_Factory varchar(06)
, @Hierarchy_Group_Code varchar(20)
, @Vendor_Style_No varchar(60)
, @Calendar_No varchar(10)
, @Rank varchar(10)
, @Description varchar(30)
, @Vendor_No varchar(06)
, @StyleColor varchar(06)
, @Style_Color_Desc varchar(30)
, @Retail_Style_No varchar(20)
, @Walmart_Style_No varchar(20)
, @Size_Cd varchar(04)
, @Current_Retail decimal(14,4)
, @Good_Better_Best varchar(10)
, @Fabric_Type varchar(10)
, @PO_Price decimal(14,4)
, @Add_Cost1 decimal(14,4)
, @Add_Cost2 decimal(14,4)
, @Add_Cost3 decimal(14,4)
, @Add_Cost4 decimal(14,4)
, @UK_Boxed varchar(10)
, @Shape varchar(10)
, @Poly_Bags varchar(10)
, @Design_Pattern varchar(10)
, @Segmentation varchar(10)
, @End_Use varchar(10)
, @Profile varchar(10)
, @Spare varchar(10)
, @OnlineLaunch_Dte VARCHAR(20)
, @Care_Instructions varchar(10)
, @Care_Instructions2 varchar(10)
, @Care_Instructions3 varchar(10)
, @Care_Instructions4 varchar(10)
, @Care_Instructions5 varchar(10)
, @Care_Label varchar(10)
, @Care_Label2 varchar(10)
, @Extra_Labels varchar(10)
, @Extra_Labels2 varchar(10)
, @Extra_Labels3 varchar(10)
, @Extra_Labels_Pos varchar(150)
, @Hanger_Lozenge_Cd varchar(10)
, @Hanger_Lozenge_Cd2 varchar(10)
, @Hanger_PolyBag varchar(10)
, @Hanger_PolyBag2 varchar(10)
, @Hanger_PolyBag3 varchar(10)
, @Label_Specification varchar(10)
, @Logo_Label varchar(10)
, @Logo_Label2 varchar(10)
, @Logo_Label3 varchar(10)
, @Logo_Label_Pos varchar(150)
, @Quality_Statement varchar(10)
, @Size_Label varchar(10)
, @Swing_Ticket_Pos varchar(150)
, @Swing_Tickets varchar(10)
, @Sleeve varchar(10)
, @Washcare_Symbol varchar(10)
, @Seasonality varchar(10)
, @Additional_Info1 varchar(17)
, @Additional_Info2 varchar(10)
, @Additional_Info3 varchar(10)
, @Total_UK_Buy_Qty int
, @Option_Status varchar(10)
, @First_Calendar_No varchar(10)
, @ToyFreebie varchar(10)
, @LicenseType varchar(10)
, @Character varchar(10)
, @NbrUKStores varchar(10)
, @WeeksOnSales varchar(10)
, @SourceTimeStamp datetime
, @ErrorMessage VARCHAR(8000) OUTPUT
, @TrxId int OUTPUT
AS
DECLARE @TrxType VARCHAR(30) = 'INT'
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
INSERT Trx_In
( TrxTypeCd , TrxAction )
VALUES
(@TrxType , 'UPDATE' )
SELECT @TrxId = @@IDENTITY
INSERT In_Style
( TrxId, Style1, TypeProduct_Cd, Season_Cd, Gender_Cd, Theme_Cd, Royalty_Cd, Vendor_Factory, Hierarchy_Group_Code
, Vendor_Style_No, Calendar_No, [Rank], [Description], Vendor_No, StyleColor, Style_Color_Desc, Retail_Style_No
, Walmart_Style_No, Size_Cd, Current_Retail, Good_Better_Best, Fabric_Type, PO_Price, Add_Cost1, Add_Cost2
, Add_Cost3, Add_Cost4, UK_Boxed, Shape, Poly_Bags, Design_Pattern, Segmentation, End_Use, [Profile], Spare
, OnlineLaunch_Dte, Care_Instructions, Care_Instructions2, Care_Instructions3, Care_Instructions4, Care_Instructions5
, Care_Label, Care_Label2, Extra_Labels, Extra_Labels2, Extra_Labels3, Extra_Labels_Pos, Hanger_Lozenge_Cd
, Hanger_Lozenge_Cd2, Hanger_PolyBag, Hanger_PolyBag2, Hanger_PolyBag3, Label_Specification, Logo_Label
, Logo_Label2, Logo_Label3, Logo_Label_Pos, Quality_Statement, Size_Label, Swing_Ticket_Pos, Swing_Tickets
, Sleeve, Washcare_Symbol, Seasonality, Additional_Info1, Additional_Info2, Additional_Info3, Total_UK_Buy_Qty
, Option_Status, First_Calendar_No, ToyFreebie, LicenseType, [Character], NbrUKStores, WeeksOnSales
)
VALUES
( @TrxId, @Style1, @TypeProduct_Cd, @Season_Cd, @Gender_Cd, @Theme_Cd, @Royalty_Cd, @Vendor_Factory, @Hierarchy_Group_Code
, @Vendor_Style_No, @Calendar_No, @Rank, @Description, @Vendor_No, @StyleColor, @Style_Color_Desc, @Retail_Style_No
, @Walmart_Style_No, @Size_Cd, @Current_Retail, @Good_Better_Best, @Fabric_Type, @PO_Price, @Add_Cost1, @Add_Cost2
, @Add_Cost3, @Add_Cost4, @UK_Boxed, @Shape, @Poly_Bags, @Design_Pattern, @Segmentation, @End_Use, @Profile, @Spare
, CAST(@OnlineLaunch_Dte AS DATETIME), @Care_Instructions, @Care_Instructions2, @Care_Instructions3, @Care_Instructions4, @Care_Instructions5
, @Care_Label, @Care_Label2, @Extra_Labels, @Extra_Labels2, @Extra_Labels3, @Extra_Labels_Pos, @Hanger_Lozenge_Cd
, @Hanger_Lozenge_Cd2, @Hanger_PolyBag, @Hanger_PolyBag2, @Hanger_PolyBag3, @Label_Specification, @Logo_Label, @Logo_Label2
, @Logo_Label3, @Logo_Label_Pos, @Quality_Statement, @Size_Label, @Swing_Ticket_Pos, @Swing_Tickets, @Sleeve, @Washcare_Symbol
, @Seasonality, @Additional_Info1, @Additional_Info2, @Additional_Info3, @Total_UK_Buy_Qty, @Option_Status, @First_Calendar_No
, @ToyFreebie, @LicenseType, @Character, @NbrUKStores, @WeeksOnSales
)
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT @ErrorMessage =
'<' + @TrxType + '> '
+ ' < SQL Error No:' + CONVERT( VARCHAR, ERROR_NUMBER() ) + '> '
+ ' < Error Desc:' + ERROR_MESSAGE() + '> '
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
IF @ErrorMessage >''
-- Email alert?
-- Stop queue processing based on Transaction Type?
SELECT ErrorMessage = @ErrorMessage
GO |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Oct 07, 2011 4:22 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
I can't read that.
Please edit your message to use [c o d e ] tags.
Please indicated clearly which part of your message indicates the error that you are experiencing. |
|
| Back to top |
|
|
| Archu |
| Posted: Mon Dec 12, 2011 8:26 pm Post subject: |
|
|
Novice
Joined: 17 Jun 2011
Posts: 16
|
This issue has been resolved with CALL statement in WMB 7.0.0.3.
We used PASSTHRU statement to call stored procedure which gave Paramater 3("") issue . But was resolved after using CALL statement.
CALL statement cannot be used in WMB 7.0.0.1. It has been fixed in WMB 7.0.0.3.
Thanks a lot for all your replies. |
|
| 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
|
|
|
|
