Copy data from MySQL database to Synapse Serverless pool Error

SoonJoo@Genting 241

Hi all,

I am trying to copy data from MySQL server to Azure Synapse Serverless Pool, I've set up the mapping of the tables but I'm getting the following error when I try to execute the pipeline:

{
"errorCode": "2200",
"message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near 'HEAP'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near 'HEAP'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near 'HEAP'.,},],'",
"failureType": "UserError",
"target": "Copy_npk",
"details": []
}

May I know what is the problem? Or anything I've missed out?

Thanks!

1 answer

MartinJaffer-MSFT 26,086 Reputation points

2021-11-12T20:37:13.157+00:00

Hello @SoonJoo@Genting and welcome to Microsoft Q&A.

By any chance, is one of your columns or tables named "HEAP"? I would imagine getting this error message if "HEAP" was a reserved keyword like "SELECT". In such a case, you need merely escape or quote the name like "[HEAP]". FYI, I'm not certain whether "HEAP" is part of MySQL language or not.
Please sign in to rate this answer.
SJ Yap 11 Reputation points

2021-11-16T07:28:28.783+00:00

Hi Martin,

I'm certain the data I am trying to copy over does not contain any columns or tables named "HEAP".

I am also having the same problem when i try to sink the data from parquet files to serverless pool:

{
"errorCode": "2200",
"message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near 'HEAP'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near 'HEAP'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near 'HEAP'.,},],'",
"failureType": "UserError",
"target": "Copy_5cp",
"details": []
}

The same problem does not occur if I'm manually load the data into the serverless pool using the right click method Azure Synapse UI.

Appreciate if anyone can give me some idea how do I go about it?

Thank you!

MartinJaffer-MSFT 26,086 Reputation points

2021-11-29T16:59:48.243+00:00

I don't think Synapse does heap indexing. Are you using a pipeline or a sql notebook to load the data?

Ronen Ariely 15,191 Reputation points

2021-11-29T17:17:57.42+00:00

Hi,

The same problem does not occur if I'm manually load the data into the serverless pool using the right click method Azure Synapse UI.

According to this, I am getting a first glance feeling that you are doing the task wrong, and when you use the GUI then the app do it for you well. In anycase, we sould clarify what exactly you do.

Please elaborate step by step what you do when this task failed - add screenshot when needed to clarify a step if you are using a GUI for example.

Amit Kamble 40 Reputation points

2023-10-17T10:06:06.35+00:00

I am also facing the same error while copying data from CSV to synapse serverless SQL external table, Is there any workaround for keyword "HEAP"? why and when does it occurs?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Copy data from MySQL database to Synapse Serverless pool Error

1 answer

Your answer