| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| String data, right truncation sql server IIB 10 |
« View previous topic :: View next topic » |
| Author |
Message
|
| daniel_aya |
 Posted: Thu Feb 04, 2021 2:03 pm Post subject: String data, right truncation sql server IIB 10 |
 |
|
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 |
|
 |
| abhi_thri |
| Posted: Thu Feb 04, 2021 11:10 pm Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
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 |
|
|
| daniel_aya |
| Posted: Fri Feb 05, 2021 10:12 am Post subject: |
|
|
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 |
|
|
| daniel_aya |
| Posted: Fri Feb 05, 2021 4:33 pm Post subject: |
|
|
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 |
|
|
|
|
|
| |
|
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
|
|
|
|
