String or binary data would be truncated. The statement has been terminated. Issue in synapse analytics

Question

String or binary data would be truncated. The statement has been terminated. Issue in synapse analytics

Rohit Kulkarni 731

Hello Team,

I am using this script :

Write object into internal table of the dedicated SQL pool

(df.write
.option(Constants.SERVER, DLH_SYNAPSE_DEDICATED_SQL_SERVER)
.option(Constants.DATA_SOURCE, SOURCE_SYSTEM_NAME)
.mode("append")
.option("preActions", "ALTER TABLE Salesforce.Case ALTER COLUMN Description varbinary(max) ;") \
.synapsesql(DLH_SYNAPSE_DEDICATED_SQL_DB_BRONZE+"."+SOURCE_SYSTEM_NAME+"."+TABLE_NAME,
Constants.INTERNAL, # Create external table type
#"/path/to/external/table") # Optional parameter that is used to specify external table's base folder; defaults to database_name/schema_name/table_name
)

To insert the data into synapse tables

There is one column Description which has a dataype of

But the data present in thie specific description column is in this format :

Continuation of this meeting series in 2021 Hi This is the invitation to the sprint review (https://en.wikipedia.org/wiki/Scrum_(software_development)#Sprint_review) of software activities in projects * Net * SmartL * Opt * Daedalus The goal of this meeting is providing a regular status update and – more importantly- give you a chance to suggest improvement. Attendance is voluntary.__________________________________________________________________________ Join Microsoft Teams Meeting\

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-10-10T19:02:12.65+00:00

Hello @Rohit Kulkarni ,

Welcome to the MS Q&A platform.

Please correct me if my understanding is wrong. salesforce.case table is auto-created from the notebook and while loading data into the table, you are seeing the error message.

as per the error message, it seems like the column Description with rows has longer than 4000 length content.

and I see you are using "ALTER TABLE Salesforce.Case ALTER COLUMN Description varbinary(max)" . so when you use 'varbinary(max)' in the alter table statement, didn't it update the datatype on the salesforce.case table?

Did you try using NVARHAR(maxStrLength)?

StringType in Spark is mapped to the NVARCHAR(maxStrLength) type in Azure Synapse. You can use maxStrLength to set the string length for all NVARCHAR(maxStrLength) type columns that are in the table with name dbTable in Azure Synapse.

https://docs.databricks.com/data/data-sources/azure/synapse-analytics.html
Rohit Kulkarni 731 Reputation points

2022-10-11T05:55:51.683+00:00

Write object into internal table of the dedicated SQL pool

(df.write
.option(Constants.SERVER, DLH_SYNAPSE_DEDICATED_SQL_SERVER)
.option(Constants.DATA_SOURCE, SOURCE_SYSTEM_NAME)
.option("maxStrLength",4000)
.option("preActions", "ALTER TABLE Salesforce.Case ALTER COLUMN Description NVARCHAR(maxStrLength) ;") \
.mode("overwrite")
.synapsesql(DLH_SYNAPSE_DEDICATED_SQL_DB_BRONZE+"."+SOURCE_SYSTEM_NAME+"."+TABLE_NAME,
Constants.INTERNAL, # Create external table type
#"/path/to/external/table") # Optional parameter that is used to specify external table's base folder; defaults to database_name/schema_name/table_name
)

After changing also have the same issue :

Py4JJavaError: An error occurred while calling o1250.synapsesqlforpython.
: com.microsoft.spark.sqlanalytics.SQLAnalyticsConnectorException: String or binary data would be truncated.
The statement has been terminated.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-10-12T00:46:50.617+00:00

Hello @Rohit Kulkarni ,
Thanks for the reply. When you alter the column with NVARCHAR(maxStrLength) did the auto-generated table column description datatype update to a different data type other than nvarchar(4000)?
did you try to alter the column directly with Ncarchar(max)?

Pankaj Jagdale 20

Hi Team , 
I am also getting same error while writing data from spark pool to dedicated sql pool this is related to datatype length:

Py4JJavaError: An error occurred while calling o4257.synapsesqlforpython.
: com.microsoft.spark.sqlanalytics.SQLAnalyticsConnectorException: Internal error - Attempt to run SQL Transaction failed with {ErrorNumber - 8152, ErrorMessage - String or binary data would be truncated.
The statement has been terminated.} 
	at  

is there any way to keep default data type varchar(max) instead of nvarchar(4000) while writing data?

Your answer

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-10-10T19:02:12.65+00:00

Hello @Rohit Kulkarni ,

Welcome to the MS Q&A platform.

Please correct me if my understanding is wrong. salesforce.case table is auto-created from the notebook and while loading data into the table, you are seeing the error message.

as per the error message, it seems like the column Description with rows has longer than 4000 length content.

and I see you are using "ALTER TABLE Salesforce.Case ALTER COLUMN Description varbinary(max)" . so when you use 'varbinary(max)' in the alter table statement, didn't it update the datatype on the salesforce.case table?

Did you try using NVARHAR(maxStrLength)?

StringType in Spark is mapped to the NVARCHAR(maxStrLength) type in Azure Synapse. You can use maxStrLength to set the string length for all NVARCHAR(maxStrLength) type columns that are in the table with name dbTable in Azure Synapse.

https://docs.databricks.com/data/data-sources/azure/synapse-analytics.html
Rohit Kulkarni 731 Reputation points

2022-10-11T05:55:51.683+00:00

Write object into internal table of the dedicated SQL pool

(df.write
.option(Constants.SERVER, DLH_SYNAPSE_DEDICATED_SQL_SERVER)
.option(Constants.DATA_SOURCE, SOURCE_SYSTEM_NAME)
.option("maxStrLength",4000)
.option("preActions", "ALTER TABLE Salesforce.Case ALTER COLUMN Description NVARCHAR(maxStrLength) ;") \
.mode("overwrite")
.synapsesql(DLH_SYNAPSE_DEDICATED_SQL_DB_BRONZE+"."+SOURCE_SYSTEM_NAME+"."+TABLE_NAME,
Constants.INTERNAL, # Create external table type
#"/path/to/external/table") # Optional parameter that is used to specify external table's base folder; defaults to database_name/schema_name/table_name
)

After changing also have the same issue :

Py4JJavaError: An error occurred while calling o1250.synapsesqlforpython.
: com.microsoft.spark.sqlanalytics.SQLAnalyticsConnectorException: String or binary data would be truncated.
The statement has been terminated.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-10-12T00:46:50.617+00:00

Hello @Rohit Kulkarni ,
Thanks for the reply. When you alter the column with NVARCHAR(maxStrLength) did the auto-generated table column description datatype update to a different data type other than nvarchar(4000)?
did you try to alter the column directly with Ncarchar(max)?
Pankaj Jagdale 20 Reputation points

2023-12-13T19:46:10.1466667+00:00

Hi Team , I am also getting same error while writing data from spark pool to dedicated sql pool this is related to datatype length: Py4JJavaError: An error occurred while calling o4257.synapsesqlforpython. : com.microsoft.spark.sqlanalytics.SQLAnalyticsConnectorException: Internal error - Attempt to run SQL Transaction failed with {ErrorNumber - 8152, ErrorMessage - String or binary data would be truncated. The statement has been terminated.} at is there any way to keep default data type varchar(max) instead of nvarchar(4000) while writing data?

Share via

String or binary data would be truncated. The statement has been terminated. Issue in synapse analytics

Write object into internal table of the dedicated SQL pool

Write object into internal table of the dedicated SQL pool

Your answer