tMSSqlSP Ouput Truncates String

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

TOS 5.3.1.r104014
Thanks Sabrina!

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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!