KB485051: How to solve the "Socket closed" error against Azure Synapse in MicroStrategy 2021 Update 2 and later

Quality Engineer, Principal • MicroStrategy

This article addresses the Socket closed error returned when executing a query to insert a large number of rows to an Azure Synapse table. This article also provides two workarounds to avoid this behavior.

Description

Starting in Strategy 2021 Update 2, parameterized query is enabled by default for transaction reports. When executing a query to insert a large number of rows (for example, 500 rows) into an Azure Synapse table, you may encounter a Query Engine error.
Sample query:


insert into tx_customer(customer_id,cust_last_name,cust_first_name,gender_id,cust_birthdate,email,address,zipcode,customer_long,customer_lat) VALUES (#0, #1,#2, #3 , #4,#5,#6,#7,#8,#9);

Error encountered:


(QueryEngine encountered error: OdbcParameterizedQuery::ExecQuery failed. Disconnected.  Error type: Odbc error. Odbc operation attempted: SQLExecute. [08S01:0: on SQLHANDLE] [Strategy][ODBC SQL Server Wire Protocol driver]Socket closed.

Why is this happening?

This is a SQL server driver issue.

Workaround #1

Change the

EncryptionMethod

option of Azure Synapse DSN from

Windows

Change the

EncryptionMethod

option via ODBC Data Source Administrator.

Select your Azure Synapse DSN and click Configure.

Go to the Security tab.

From the Encryption Method drop-down, choose 1-SSL.
Click OK to save modification.

Linux/UNIX

Change the

EncryptionMethod

option via the

odbc.ini

file.


[WH_T26.AzureSynapseAnalytics]
Description=Strategy ODBC Driver for SQL Server Wire Protocol
DoubleToStringPrecision=17
CryptoProtocolVersion=TLSv1.2
Driver=/iserver-install/BIN/Linux/lib/MYsqlsXX.so
EncryptionMethod=1
TrustStore=
ValidateServerCertificate=0
Database=F9458_2
EnableQuotedIdentifiers=1
IANAAppCodePage=106
PortNumber=1433
HostNameInCertificate=
AnsiNPW=1
HostName=dbcert.datab

Workaround #2

Disable parameterize query via the Database Instance dialog. This issue does not exist when parameterized query is off.

Select your Azure Synapse database instance and click Modify.

Go to the Advanced tab and deselect the Use parameterized queries option.

Click OK to save the change.

Comment

0 comments

Details

Knowledge Article

Published:

June 3, 2021

Last Updated:

June 30, 2021

Description

insert into tx_customer(customer_id,cust_last_name,cust_first_name,gender_id,cust_birthdate,email,address,zipcode,customer_long,customer_lat) VALUES (#0, #1,#2, #3 , #4,#5,#6,#7,#8,#9);

Error encountered:

(QueryEngine encountered error: OdbcParameterizedQuery::ExecQuery failed. Disconnected. Error type: Odbc error. Odbc operation attempted: SQLExecute. [08S01:0: on SQLHANDLE] [Strategy][ODBC SQL Server Wire Protocol driver]Socket closed.

[WH_T26.AzureSynapseAnalytics] Description=Strategy ODBC Driver for SQL Server Wire Protocol DoubleToStringPrecision=17 CryptoProtocolVersion=TLSv1.2 Driver=/iserver-install/BIN/Linux/lib/MYsqlsXX.so EncryptionMethod=1 TrustStore= ValidateServerCertificate=0 Database=F9458_2 EnableQuotedIdentifiers=1 IANAAppCodePage=106 PortNumber=1433 HostNameInCertificate= AnsiNPW=1 HostName=dbcert.datab