topic Re: tMSSqlSP Ouput Truncates String in Talend Studio

tMSSqlSP Ouput Truncates String

Anonymous — Sat, 16 Nov 2024 11:51:38 GMT

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?

Re: tMSSqlSP Ouput Truncates String

Anonymous — Thu, 24 Oct 2013 03:55:44 GMT

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

Re: tMSSqlSP Ouput Truncates String

Anonymous — Thu, 24 Oct 2013 13:05:11 GMT

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

Re: tMSSqlSP Ouput Truncates String

Anonymous — Fri, 25 Oct 2013 04:34:13 GMT

Hi,
Which talend build version are you using? I will take a testing for it to see if it repro again.
Best regards
Sabrina

Re: tMSSqlSP Ouput Truncates String

Anonymous — Fri, 25 Oct 2013 12:32:57 GMT

TOS 5.3.1.r104014
Thanks Sabrina!

Re: tMSSqlSP Ouput Truncates String

Anonymous — Tue, 29 Oct 2013 11:50:37 GMT

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

Re: tMSSqlSP Ouput Truncates String

Anonymous — Wed, 30 Oct 2013 03:56:25 GMT

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

Re: tMSSqlSP Ouput Truncates String

Anonymous — Wed, 30 Oct 2013 11:45:13 GMT

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_USEvergreenSearch_SQLServer2012&CR_CC=200072479

Re: tMSSqlSP Ouput Truncates String

Anonymous — Thu, 31 Oct 2013 03:55:19 GMT

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-nvarcharmax

Best regards
Sabrina

Re: tMSSqlSP Ouput Truncates String

Anonymous — Thu, 31 Oct 2013 11:56:35 GMT

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!

Re: tMSSqlSP Ouput Truncates String

Anonymous — Fri, 01 Nov 2013 02:41:27 GMT

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

Re: tMSSqlSP Ouput Truncates String

Anonymous — Wed, 06 Nov 2013 14:39:36 GMT

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

Re: tMSSqlSP Ouput Truncates String

Anonymous — Thu, 07 Nov 2013 06:42:23 GMT

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

Re: tMSSqlSP Ouput Truncates String

Anonymous — Thu, 07 Nov 2013 14:02:11 GMT

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

Re: tMSSqlSP Ouput Truncates String

Anonymous — Fri, 08 Nov 2013 03:39:36 GMT

Hi,
Thanks your contribution to talend.
Best regards
Sabrina