How to capture Data truncation?

Five Stars

How to capture Data truncation?

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
Community Manager

Re: How to capture Data truncation?

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

Re: How to capture Data truncation?

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
One Star

Re: How to capture Data truncation?

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?
One Star

Re: How to capture Data truncation?

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?
One Star

Re: How to capture Data truncation?

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

Re: How to capture Data truncation?

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
Five Stars

Re: How to capture Data truncation?

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
One Star

Re: How to capture Data truncation?

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???
Moderator

Re: How to capture Data truncation?

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
--
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: How to capture Data truncation?

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

Re: How to capture Data truncation?

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
--
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: How to capture Data truncation?

Reject option is not reflecting
Moderator

Re: How to capture Data truncation?

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
--
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: How to capture Data truncation?

Reject option is not reflecting

Right click on the tMSSQLOutput and select Row then Rejects.
Moderator

Re: How to capture Data truncation?

Hi kunalrane
Is there any update for your issue, don't hesitate to post it on forum.
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: How to capture Data truncation?

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

Re: How to capture Data truncation?

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
Moderator

Re: How to capture Data truncation?

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
--
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: How to capture Data truncation?

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

Re: How to capture Data truncation?

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

Hi Sabrina, Merry Christmas,
Any Update on this issue???
Moderator

Re: How to capture Data truncation?

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

Sorry for delay. I'm making a testing for your current case to see if there is any better solution for SQL SERVER then 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: How to capture Data truncation?

Hi Sabrina,
Any progress on this issue???
One Star

Re: How to capture Data truncation?

Hi Sabrina,
I hope you are doing well...
Any update on above mentioned thing???
Moderator

Re: How to capture Data truncation?

Hi,
Sorry for delay.
For your requirement, would you mind opening a work item jira issue of DI project so that our develop will check if SQL Server can get the specified column on which truncation happens. Thanks for your understanding.
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.