Cannot create row size greater than 8060 in synapse as target

[gayatri235]

Hi All,

We are replicating data from postgresql to synapse fullload +CDC

while cdc we are getting this error  attaching the snip.

Its says the rw size cannot be greater than 8060 ..

checked at source the row_size is not greater than 8060 .

Any tips for troubleshooting this error.

[DesmondWOO · Accepted solution]

Hi @gayatri235 ,

Thank you for reaching out to Qlik Community.

According to the error message, greater than row size of 8060 is Synapse limitation. 

 As you mentioned that the row_size of source data is not greater than 8060, could you tell us
- DDL of the source table and target table
- Transformation rule if you have

If the information provided above is confidential, I would recommend submitting a support ticket.

Regards,
Desmond

[SachinB]

Hello @gayatri235 ,

Thank you for reaching out to the Qlik community!

8060 bytes is the limit size of a row within SQL Server/Azure. You can read more about Large Row Support in this link.

Row size error when transferring data from Azure Synapse serverless pool to Azure SQL DB - Microsoft...

There aren't any methods to overcome this limit. 

Work around:

There's an expression function on Replicate that can be used to retrieve the extra data that exceeds the 8060 row count and put it into another column. You will need to add a new column and use the source lookup function to retrieve the extra data.

Please refer to Data Enrichment functions for an example of how to use source lookup

https://help.qlik.com/en-US/replicate/May2021/Content/Global_Common/Content/SharedEMReplicate/Custom...

Regards,

Sachin B

[gayatri235]

how do I identify these columns  ?

[gayatri235]

Hi , I did open a support ticket.

[SachinB]

Hello @gayatri235 ,

In order to check or to identify the columns, You can use below query for the same.

SELECT *
FROM YourTableName -- Replace 'YourTableName' with the name of your table
WHERE DATALENGTH(column1) + DATALENGTH(column2) + ... > 8060

Hope this helps for you!

Regards,
Sachin B

[aarun_arasu]

Hello @gayatri235 ,

You may also try the below approach

SELECT * FROM your_table ORDER BY LENGTH(CONCAT_WS('', col1, col2, col3, ...)) DESC LIMIT 1;

concatenate all columns into a single string, using an empty string as the separator and then calculate the length of this combined string.

Regards

Arun