Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
desmondchew
Creator III
Creator III

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

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

Labels (1)
4 Replies
Dana_Baldwin
Support
Support

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
Creator III
Creator III
Author

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
Support
Support

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
Specialist III
Specialist III

@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