A table unable to full load due to SQL Error SQLState 42601

[desmondchew]

We are running full load, CDC and store capture. Source endpoint is a SQL server while target endpoint is Snowflake.

We are hitting errorRetCode: SQL_ERROR SqlState: 42601 NativeError: 1810 Message: SQL compilation error: error line 1 at position 175

Invalid character length: 104,857,600.RetCode: SQL_ERROR SqlState: 42601 NativeError: 1810 Message: SQL compilation error: error line 1 at position 463
Invalid character length: 104,857,600. Must be between 1 and 16,777,216.; Execute create table statement failed, statement CREATE TABLE 

How can we resolve this?

Thanks
Desmond

[Dana_Baldwin]

Hi @desmondchew 

This is a snowflake limitation as stated here:
https://docs.snowflake.com/en/sql-reference/data-types-text.html

I suspect there are LOB columns involved; please change the task setting 'Limit LOB size to (KB)' to 16384 (or lower based on needs) and run the task again.

Thanks,

Dana

[desmondchew]

Hi Dana,

I have limit LOB size to (KB) 16300, however the task refuse to load. According to the error log it seems to be finding a scn in redo log. I have setup this task fresh creating a new task and did a full load. Why is it looking for a scn?

Stream component 'st_0_odsprod_array' terminated
Stream component failed at subtask 0, component st_0_odsprod_array
Error executing command
Failed to set stream position on context '00000000.8b0043b4.00000001.005e.01.0000:820097.2924261.16'
The provided scn 000000008B0043B4 is not found in Redo log, thread 1

Thank you.

Desmond

[SachinB]

Hello @desmondchew ,

Could you please create support case for the same and provide appropriate logs, We will investigate the issue and will provide analysis for the same?

Regards,

Sachin B

[Heinvandenheuvel]

@desmondchew  Missing SCN would be something completely different from exceeding LOB size.  Is the max lob size issue fixed? Be sure to carefully read the Snowflake limitation link above paying attention to the BYTES vs CHARS issue. The limit is 16MB BYTES, which may be 4MB CHARS  for unicode-4-bytes-per-char.

For the missing SCN... how far back are you trying to resume from? There is a limit within Oracle of about 5 days  for SCN usage history https://www.red-gate.com/simple-talk/databases/oracle-databases/oracle-system-change-number-an-intro...   which may play a rolde. Can you resume by timestamp?

Hein