topic Re: How to capture Data truncation? in Talend Studio

How to capture Data truncation?

briansms — Sat, 16 Nov 2024 14:16:25 GMT

How can "Data truncation" messages from a DBOutput be captured? I have it's Rejects flow going to a file, but none of the data truncation messages are in there.
Thanks,
- Brian

Re: How to capture Data truncation?

Anonymous — Fri, 01 Aug 2008 04:04:05 GMT

Hello
Which version of TOS are you using?
I tested this issue with TOS2.4.1, it shows the 'data truncation' error message.
Best regards

shong

Re: How to capture Data truncation?

briansms — Fri, 01 Aug 2008 13:59:17 GMT

Hi Shong,
I'm using both 2.3.3 and 2.4.1, but it's my mistake. Apparently I was looking at the wrong table.
I do notice however that your message above includes the field name in the message: "Data truncation: Data too long for field 'name'..."
The message from MSSSQLOutput is simply "Data truncation", it doesn't tell which field has the offending data. How do you get the field name in there? (I'm outputting the rejects row to a tFileOutputDelimited)

classstructureid|assetattrid|measureunitid|domainid|defaultnumvalue|defaultalnvalue|attrdescprefix|useinitemspec|itemsequence|itemrequirevalue|useinitemdesc|useinassetspec|assetsequence|assetrequirevalue|useinassetdesc|useinlocspec|locsequence|locrequirevalue|useinlocdesc|cs01|cs02|cs03|cs04|cs05|orgid|section|siteid|classspecid|rowstamp|errorCode|errorMessage
2007|CHRGCIRC1||||||0|4|0|0|1|4|0|1|1|4|0|0|||||||||1093|||Data truncation
2007|CHRGCIRC2||||||0|5|0|0|1|5|0|1|1|5|0|0|||||||||1094|||Data truncation
2007|CHRGCIRC3||||||0|6|0|0|1|6|0|1|1|6|0|0|||||||||1095|||Data truncation
2007|HEATINPUT||||||0|7|0|0|1|7|0|1|1|7|0|0|||||||||1096|||Data truncation

Thanks,
- Brian

Re: How to capture Data truncation?

_AnonymousUser — Thu, 08 Dec 2011 19:34:38 GMT

I am getting the same thing.. I am not given column name for which data truncation occurs.. i am storing rejected rows in file and sending it in email..It is not telling me which column is truncated.
Can anyone help?

Re: How to capture Data truncation?

_AnonymousUser — Thu, 08 Dec 2011 19:35:56 GMT

Re: How to capture Data truncation?

_AnonymousUser — Thu, 22 Dec 2011 10:57:23 GMT

Hi,
Please check whether the input & output columns are in the same order.
Regards
Deepak

Re: How to capture Data truncation?

Anonymous — Fri, 13 Dec 2013 07:36:16 GMT

I am also facing issue...where i am using tmssqloutput
I want to know on which column the data truncation is happening so I can rectify the same.
Kindly find the actual scenario in linked screenshot below
Please help

Re: How to capture Data truncation?

Anonymous — Sat, 14 Dec 2013 16:55:10 GMT

used tSchemaComplianceCheck component after tMap and before your tmssqloutput component and then used reject link from tSchemaComplianceCheck component it will give you data truncation with column name and error code.
Hope this will help you

Re: How to capture Data truncation?

Anonymous — Thu, 19 Dec 2013 07:29:57 GMT

Hey Umesh, thanks for reply.
i have tried the same as you have suggested but not working.
In my source file City field contains 'Mumbai'(6 characters) but my corresponding CITY column of table is length 5.
It should give me the error like "Data truncation on CITY column....."
How can i get it???

Re: How to capture Data truncation?

Anonymous — Thu, 19 Dec 2013 07:41:18 GMT

Hi,
Here is KB article on Talend Help Center about Exception Data truncation Data too long for column, hope it will be helpful.
Best regards
Sabrina

Re: How to capture Data truncation?

Anonymous — Thu, 19 Dec 2013 09:59:41 GMT

Dear Sabrina,
Thanks for sharing link....but i have not found it useful.
I am able to identify the issue i.e.Data Truncation but what is Next??
How should i come to know on which column the truncation happens??
I want the column name in my message.....
I have attached the screenshot of Database table output in which I am catching Errors.

Re: How to capture Data truncation?

Anonymous — Thu, 19 Dec 2013 10:19:23 GMT

Hi,
You can link "Rejects" row from tMssqloutput to check which column is rejected.
"Rejectes" is available when unchecked option " Use Batch Size".
Best regards
Sabrina

Re: How to capture Data truncation?

Anonymous — Thu, 19 Dec 2013 10:36:05 GMT

Reject option is not reflecting

Re: How to capture Data truncation?

Anonymous — Fri, 20 Dec 2013 02:56:33 GMT

Hi,

Reject option is not reflecting

It means there is no rejected row? If so, you can check the 'die on error' option on tOracleOutput and run the job again, then to see if there any detailed error message are printed on the console.
Best regards
Sabrina

Re: How to capture Data truncation?

janhess — Fri, 20 Dec 2013 09:22:21 GMT

Reject option is not reflecting

Right click on the tMSSQLOutput and select Row then Rejects.

Re: How to capture Data truncation?

Anonymous — Mon, 23 Dec 2013 02:56:07 GMT

Hi kunalrane
Is there any update for your issue, don't hesitate to post it on forum.
Best regards
Sabrina

Re: How to capture Data truncation?

Anonymous — Mon, 23 Dec 2013 06:51:24 GMT

Hi Sabrina,
I have tried that to get the reject link but not showing but when i have shutdown the Talend and my machine then i got the link of reject when i have right clicked on tmssqloutput
So now i am transferring the rejected records in Text file but still i am not getting the specific Column name on which data truncation happens.
kindly check the same in screenshots.

Re: How to capture Data truncation?

Anonymous — Mon, 23 Dec 2013 07:23:09 GMT

Hi Sabrina,
One more issue...
As you suggested...i have uncheck the Die on error of tmssqloutput then i got the reject link
The issue is...
i am capturing database connectivity issue also(like table_name is invalid object) for that i am using tlogcather and tlogcather listens to Die on Error and if i'l uncheck the Die on Error option of tmssqloutput,i can not log the database connectivity issue.
Can you help me how can i handle the both the scenario in one board

Re: How to capture Data truncation?

Anonymous — Mon, 23 Dec 2013 07:34:38 GMT

Hi,
There is no specific Column name in error messgae(just line1;line2;....).
You have to use "Die on Error" or rejected in two board. The "rejected" row is unavailable if you checked out "Die on error".

you can check the 'die on error' option on tOracleOutput and run the job again, then to see if there any detailed error message are printed on the console.

.
Best regards
Sabrina

Re: How to capture Data truncation?

Anonymous — Mon, 23 Dec 2013 10:19:51 GMT

Hi Sabrina,
I have tried the same thing with Oracle output.
And i got the specified column on which truncation happens.
Thanks for the same but what about SQL Server Output.
I am trying to Use Sql server.
Kindly help me to sort this out.