Six Stars

Problem of memory[DataTruncation]

Hi,

I try to insert 2635 file (3.50Go) in SQL Server Standard 2014. So i created a job which allows me to save data in a table tMSSQLOutput_1 and errors in a table tMSSQLOutput_2 in case there is a primary key duplication.

 Everything works well at the beginning of the execution until the appearance of an error in tMSSQLOutput_2.

 

Here is a screenshot of my job

c1.PNGc2.PNG

 

 

And when i try to select all from the table in SQL SERVER to see the result of 1410 file already inserted, i got this error :

Une erreur s'est produite lors de l'exécution du lot. Message d'erreur : Une exception de type 'System.OutOfMemoryException' a été levée.

 

Note: my pc have 16Go of ram and i've changed my JVM settings like this 

c3.PNG

I work with Talend 6.3.1 and java 8

 

I need your help ! 

Thank you in advance.

Best regards

1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars TRF
Seven Stars

Re: Problem of memory[DataTruncation]

Just try to trunk it on Talend side before to change in database.

TRF
4 REPLIES
Seven Stars TRF
Seven Stars

Re: Problem of memory[DataTruncation]

Hi,

Maybe just due to a truncation of one of the column involved in tMSSQLOutput_2.

As the error occurs for the 1st row you try to insert into the table associated to tMSSQLOutput_2, you should check carefully the length for all varchar columns on MS SQL side.

http://stackoverflow.com/questions/14516348/how-to-see-in-which-column-data-truncation-has-happened


TRF
Ten Stars

Re: Problem of memory[DataTruncation]

I suspect this is two issues. The first one is likely down to the fact that your schema is incorrect. I suspect a column you are trying to insert is too big for the column in the DB table. This can happen for several reasons. One situation which can cause this is when you create a DB schema using Talend based on a schema from a flat file which Talend has "guessed" the column widths of. When you create the schema in the DB using Talend it sets the column widths according to this "guess". The guess is based on the first (so many rows...not sure off the top of my head exactly how many). So, check the size of your columns in your DB and your data.

 

The memory issue could be several things. First of all, is this memory issue seen in Talend or your SQL Server query tool? If it is seen in the SQL Server query tool then you need to speak to a DBA. If it is in Talend and seen after your job has run in the Studio, first just restart Talend. Talend Studio is not great once it has been pushed regarding memory. Processing a 3.5GB several times (with errors) could very well tip it over the edge. I suspect that will help.

Rilhia Solutions
Six Stars

Re: Problem of memory[DataTruncation]

Thanks for your replay.

Maybe I have a column (errorMessage) in tMSSqlOutput_2 ... exceeds 255 characters.

I will change the size and see it will resolve the problem or not.

 

Seven Stars TRF
Seven Stars

Re: Problem of memory[DataTruncation]

Just try to trunk it on Talend side before to change in database.

TRF