topic Re: Error with tMSSQLRow Reject Functionality in Talend Studio

Error with tMSSQLRow Reject Functionality

qfinleymha — Sat, 16 Nov 2024 03:45:54 GMT

I have created a job which passes values to tMSSQLRow, with Die on Error unchecked and a rejects stream, and I am able to catch reject rows. However, the stream is causing an issue with committing all statements in tMSSQLRow. The exact scenario is that X number of records are good and do not throw a SQL error. The console says that X number of records have been committed, however, when i query the table, the number of records is less than X. This mismatch occurs when one of the records are rejected due to SQL error: Conversion failed when converting date and/or time from character string.

Any insight on why this is happening for a particular SQL Exception?

Re: Error with tMSSQLRow Reject Functionality

Anonymous — Fri, 20 Dec 2019 08:53:48 GMT

Hello,

What are your statements and the query you are running in tMSSQLRow component? Are you using stored procedure?

Best regards

Sabrina

Re: Error with tMSSQLRow Reject Functionality

qfinleymha — Fri, 20 Dec 2019 14:48:13 GMT

Hi Sabrina,

Each row passed to tMSSQLRow creates an 'INSERT INTO Select' statement. There is no stored procedure. I am using tMSSQLRow instead of tMSSQLOutput because each row contains a different set of columns to insert into. For example,

tDenormalize (comma separated) --------> tMSSQLROW

row1.columns INSERT INTO tableName (" + row1.columns + ") SELECT " + row1.values + "

row1.values

When i reduce the 'Commit Every' to 1 OR use existing connection and opt for 'Auto Commit', the records load as expected.

I would not like to commit on every transaction because this job needs to process large data sets (~1M records)