ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportString data, right truncation sql server IIB 10

Post new topicReply to topic
String data, right truncation sql server IIB 10 View previous topic :: View next topic
Author Message
daniel_aya
PostPosted: Thu Feb 04, 2021 2:03 pm Post subject: String data, right truncation sql server IIB 10 Reply with quote

Newbie

Joined: 04 Feb 2021
Posts: 3

Hi guys, I'm facing a problem when executing Sql Server stored procedure from esql. I'm getting:
Code:
 string data, right truncation
exception.

Below is the stored procedure code:

Code:

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
PROCEDURE SelectDocument
   @DocumentDate as date,
   @DocumentID as nvarchar(20)
AS
BEGIN
   SET NOCOUNT ON;
   select * from Documents where DocID = '123' and DocDate = '2020-07-31'
END


As you can see I hard coded query values in an attempt to solve the problem.

Next you'll see my esql code

Code:

BROKER SCHEMA TestApp.Flow

CREATE COMPUTE MODULE InOutTransform
   CREATE FUNCTION Main() RETURNS BOOLEAN
   BEGIN
      DECLARE result REFERENCE TO OutputRoot.XMLNSC.Result;
      DECLARE docDate DATE;
      SET docDate = DATE '2020-07-31';
      CALL SelectDocument(docDate, 'B', result.Doc[]);   
      SET OutputRoot.XMLNSC.Documents = result.Doc;
   RETURN TRUE;
   END;
END MODULE;

CREATE PROCEDURE SelectDocument(IN DocumentDate DATE, IN DocumentID CHARACTER)
   LANGUAGE DATABASE
   DYNAMIC RESULT SETS 1
   EXTERNAL NAME "dbo.SelectDocument";


I already tried some things in order to solve this issue:

1. If the SP has only one parameter; whereas it's Date or ID parm, I got expected response from database using esql code. It's the same if there is not parameter.

2. I alternated params order; but the issue remain.

3. I checked ODBC configuration, but I don't see anything related to this problem.

Thanks in advance.
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Thu Feb 04, 2021 11:10 pm Post subject: Reply with quote

Chevalier

Joined: 17 Jul 2017
Posts: 418
Location: UK

hi...can you please post the full error details

Quote:
If the SP has only one parameter; whereas it's Date or ID parm, I got expected response from database using esql code. It's the same if there is not parameter.


just to confirm, when you tried with single param and without any params you say it worked fine but not with the two params...did the stored procedure logic remained the same through this excercise?
Back to top
View user's profile Send private message
daniel_aya
PostPosted: Fri Feb 05, 2021 10:12 am Post subject: Reply with quote

Newbie

Joined: 04 Feb 2021
Posts: 3

Hello abhi_thri, Thank you for your interest.

Yes, when I tried to invoke the stored procedure with only one parameter (no matter what parameter) or even without parameters, I got expected response. I did those test changing parameters at IBM and Sql Server, accordingly.

Below it's full error response from IIB,

Code:


<Error>
 <RecoverableException>
  <File>F:\ci\slot1\product-build\WMB\src\DataFlowEngine\SQLNodeLibrary\ImbComputeNode.cpp</File>
  <Line>515</Line>
  <Function>ImbComputeNode::evaluate</Function>
  <Type>ComIbmComputeNode</Type>
  <Name>TestApp/Flow/TestAppFlow#FCMComposite_1_3</Name>
  <Label>TestApp.Flow.TestAppFlow.Transformation</Label>
  <Catalog>BIPmsgs</Catalog>
  <Severity>3</Severity>
  <Number>2230</Number>
  <Text>Caught exception and rethrowing</Text>
  <Insert>
   <Type>14</Type>
   <Text>TestApp.Flow.TestAppFlow.Transformation</Text>
  </Insert>
  <RecoverableException>
   <File>F:\ci\slot1\product-build\WMB\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp</File>
   <Line>792</Line>
   <Function>SqlStatementGroup::execute</Function>
   <Type></Type>
   <Name></Name>
   <Label></Label>
   <Catalog>BIPmsgs</Catalog>
   <Severity>3</Severity>
   <Number>2488</Number>
   <Text>Error detected, rethrowing</Text>
   <Insert>
    <Type>5</Type>
    <Text>TestApp.Flow.InOutTransform.Main</Text>
   </Insert>
   <Insert>
    <Type>5</Type>
    <Text>9.3</Text>
   </Insert>
   <Insert>
    <Type>5</Type>
    <Text>CALL SelectDocument(docDate, 'B', result.Doc[]);</Text>
   </Insert>
   <RecoverableException>
    <File>F:\ci\slot1\product-build\WMB\src\DataFlowEngine\ImbRdl\ImbRdlRoutine.cpp</File>
    <Line>1547</Line>
    <Function>SqlRoutine::invoke</Function>
    <Type></Type>
    <Name></Name>
    <Label></Label>
    <Catalog>BIPmsgs</Catalog>
    <Severity>3</Severity>
    <Number>2934</Number>
    <Text>Error occured in procedure</Text>
    <Insert>
     <Type>5</Type>
     <Text>SelectDocument</Text>
    </Insert>
    <DatabaseException>
     <File>F:\ci\slot1\product-build\WMB\src\DataFlowEngine\MessageServices\ImbOdbc.cpp</File>
     <Line>3851</Line>
     <Function>ImbOdbcStatement::checkRcInner</Function>
     <Type></Type>
     <Name></Name>
     <Label></Label>
     <Catalog>BIPmsgs</Catalog>
     <Severity>3</Severity>
     <Number>2321</Number>
     <Text>Root SQL exception</Text>
     <Insert>
      <Type>2</Type>
      <Text>-1</Text>
     </Insert>
     <Insert>
      <Type>14</Type>
      <Text>odbc32.dll</Text>
     </Insert>
     <DatabaseException>
      <File>F:\ci\slot1\product-build\WMB\src\DataFlowEngine\MessageServices\ImbOdbc.cpp</File>
      <Line>4073</Line>
      <Function>ImbOdbcStatement::checkRcInner</Function>
      <Type></Type>
      <Name></Name>
      <Label></Label>
      <Catalog>BIPmsgs</Catalog>
      <Severity>3</Severity>
      <Number>2322</Number>
      <Text>Child SQL exception</Text>
      <Insert>
       <Type>5</Type>
       <Text>22001</Text>
      </Insert>
      <Insert>
       <Type>2</Type>
       <Text>0</Text>
      </Insert>
      <Insert>
       <Type>5</Type>
       <Text>[Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation</Text>
      </Insert>
     </DatabaseException>
    </DatabaseException>
   </RecoverableException>
  </RecoverableException>
 </RecoverableException>
</Error>

Back to top
View user's profile Send private message
daniel_aya
PostPosted: Fri Feb 05, 2021 4:33 pm Post subject: Reply with quote

Newbie

Joined: 04 Feb 2021
Posts: 3

Hi guys. I solved it by creating another ODBC connector. First I created a ODBC Driver 17 for SQL Server DSN; afterwards I created a SQL Server Native Client 11.0 DSN, in order to test possible solutions. Apparently there is an issue between Driver 17 and IIB 10.
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportString data, right truncation sql server IIB 10
Jump to:



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
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.