One Star

tMSSqlSP Ouput Truncates String

When using tMSSqlSP component to call SQL Server stored procedure, input from Talend string to a SQL varchar(MAX) works fine. But output from SQL varchar(MAX) to Talend string gets truncated to 4,000 characters. How do I work around this? Is this a bug?
14 REPLIES
Moderator

Re: tMSSqlSP Ouput Truncates String

Hi,
Could you please check the KB article about Exception Data truncation Data too long for column to see if it is useful for your case?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: tMSSqlSP Ouput Truncates String

Sabrina,
Unfortunately, the link you provided above did not help me. I am not getting any errors. Talend simply truncates my string down to 4,000 characters.
Attached is the simplest example I can provide that you can try for yourself.
String in tFixedFlowInput1 is 4,004 characters long.
On the last screen, tLogRow1 displays both Input and Output, and 4 characters have been truncated on the Output down to 4,000 characters.
Below is the MS SQL Stored Procedure.
If I run below stored procedure outside of Talend, I get the full Output of 4,004 characters.


USE
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE .
@Input varchar(Max),
@Output varchar(Max) OUT
AS
BEGIN
SET @Output = @Input
END
GO
Moderator

Re: tMSSqlSP Ouput Truncates String

Hi,
Which talend build version are you using? I will take a testing for it to see if it repro again.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: tMSSqlSP Ouput Truncates String

TOS 5.3.1.r104014
Thanks Sabrina!
One Star

Re: tMSSqlSP Ouput Truncates String

Hi Sabrina,
Did you get a chance to test this? Thanks for your help
Chung
Moderator

Re: tMSSqlSP Ouput Truncates String

Hi,
Sorry for delay.
I have made a testing on Mysql, everything is OK. I suspect something is limited by oracle varchar size (1, 4000). Is it Ok with you when you use blob data type instead of varchar for your 4,004 characters?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: tMSSqlSP Ouput Truncates String

Hi Sabrina,
Thanks for testing!
My case is for Microsoft SQL Server, not Oracle.
For MSSQL:
* varchar supports up to 8,000 characters.
* varchar(MAX) supports up to 2 GB (2,147,483,647 characters).
In my example:
* passing data from Talend to MSSQL works fine with varchar of 4,004 character. No truncation.
* passing data from MSSQL to Talend does not work. It truncates varchar of 4,004 characters down to 4,000 with no error msg.
This only happens for tMSSqlSP component.
This does not happen for tMSSqlInput or tMSSqlOutput components. These two components work just fine with varchar of 4,004 characters with no truncations.
But I need to call stored procedures so I have to use tMSSqlSP.
Could you please test Microsoft SQL Server? I believe you can download and use the trial version from Microsoft for 180 days: http://technet.microsoft.com/en-US/evalcenter/hh225126?WT.srch=1&WT.mc_id=SEM_GOOGLE_USEvergreenSear...
Moderator

Re: tMSSqlSP Ouput Truncates String

Hi,
For MSSQL:
* varchar supports up to 8,000 characters.
* varchar(MAX) supports up to 2 GB (2,147,483,647 characters).

What's your encoding? If encode is different, spaces for each character are also different. You can only save 4,000 if it is a two-digit.
Please see this article http://stackoverflow.com/questions/14030498/how-does-sql-server-store-more-than-4000-characters-in-n...

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: tMSSqlSP Ouput Truncates String

Hi Sabrina,
I am using 8-bit encoding. But it really doesn't matter since both varchar(MAX) and nvarchar(MAX) support gigabytes of data. Below are the results I am seeing using exactly the same encoding:
Using tMSSqlInput with varchar(MAX), no issues passing 4,004 characters
Using tMSSqlOutput with varchar(MAX), no issues passing 4,004 characters
Using tMSSqlSP (IN) with varchar(MAX), no issues passing 4,004 characters
--> Using tMSSqlSP (OUT) with varchar(MAX), 4,004 characters is truncated to 4,000 characters
I can use .NET application and call the same stored procedure and get all 4,004 character out. So, I really don't think the issue is with my stored procedure or Microsoft SQL Server. This only happens when I use Talend.
Were you able to install the free Microsoft SQL Server from the link I sent to confirm this? Thanks for your continued support!
Moderator

Re: tMSSqlSP Ouput Truncates String

Hi,
Thanks your response.
I will make a testing on MSSQL server to see if this issue repro and come back to you asap.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: tMSSqlSP Ouput Truncates String

Hi Sabrina, did you get a chance to test this?
Moderator

Re: tMSSqlSP Ouput Truncates String

Hi,
Sorry for delay.
I have made a testing on Microsoft SQL Server.
I suspect it is a bug.
Could you please open a jira issue of DI project on Talend Bug tracker. Our component developer will check it.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: tMSSqlSP Ouput Truncates String

Thanks Sabrina. I've opened an issue: https://jira.talendforge.org/browse/TESB-11968#
Moderator

Re: tMSSqlSP Ouput Truncates String

Hi,
Thanks your contribution to talend.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.