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

Question

We have a table in Azure Synapse Serverless pool which has 500+ columns and has approx 2.5 lac records and datatype of multiple columns are nvarchar, decimal, int, etc.

We are moving data from this table to an Azure SQL DB table using Copy activity and we are using Auto Create table sink option.

Receiving below error when pipeline is executing:

Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Cannot create a row of size 8277 which is greater than the allowable maximum row size of 8060. The statement has been terminated.

Answer 1

Hi @Priya Jha ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question.

As I understand your query, you are trying to load data from Azure Synapse SQL pool to Azure SQL server , however, for some records, the size of data is exceeding the limit of 8060 bytes and is throwing error. Please let me know if my understanding about your query is incorrect.

Prior to SQL Server 2016 , the in-row data size of a memory-optimized table couldn't be longer than 8,060 bytes. However, starting SQL Server 2016 (13.x) and in Azure SQL Database it is now possible to create a memory-optimized table with multiple large columns (e.g., multiple varbinary(8000) columns) and LOB columns (i.e., varbinary(max), varchar(max), and nvarchar(max)) and perform operations on them using natively compiled T-SQL modules and table types.

Columns that do not fit in the 8060 byte row size limit are placed off-row, in a separate internal table. Each off-row column has a corresponding internal table, which in turn has a single nonclustered index. For details about these internal tables used for off-row columns see sys.memory_optimized_tables_internal_attributes .

Certain limitations and caveats to using off-row columns in a memory-optimized table are listed below:

• If there is a columnstore index on a memory-optimized table, then all the columns must fit in-row.
• All index key columns must be stored in-row. If an index key column doesn't fit in-row, adding the index fails.
• Caveats on altering a memory-optimized table with off-row columns.
• For LOBs the size limitation mirrors that of disk based tables (2GB limit on LOB values).
• For optimal performance, it is recommended to have most columns fit within 8060 bytes.

Kindly give a read to this article to get more information : Table and Row Size in Memory-Optimized Tables

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators