Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
qfinleymha
Contributor
Contributor

Error with tMSSQLRow Reject Functionality

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?

Labels (3)
2 Replies
Anonymous
Not applicable

Hello,

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

Best regards

Sabrina

qfinleymha
Contributor
Contributor
Author

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)