Compatibility issues with third-party applications and Azure Synapse Analytics
Applications built for SQL Server will seamlessly work with Azure Synapse dedicated SQL pools. In some cases, however, features and language elements that are commonly used in SQL Server may not be available in Azure Synapse, or they may behave differently.
Common issues
This article lists common issues you may come across when using third-party applications with Azure Synapse Analytics.
Tableau error: "An attempt to complete a transaction has failed. No corresponding transaction found"
Starting from Azure Synapse dedicated SQL pool version 10.0.11038.0, some Tableau queries making stored procedure calls may fail with the following error message: "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]111214; An attempt to complete a transaction has failed. No corresponding transaction found."
Cause
This is an issue in Azure Synapse dedicated SQL pool caused by the introduction of new system stored procedures that are called automatically by the ODBC and JDBC drivers. One of those system stored procedures can cause open transactions to be aborted if they fail execution. This issue can happen depending on the client application logic.
Solution
Customers seeing this particular issue when using Tableau connected to Azure Synapse dedicated SQL pools should set FMTONLY to YES in the SQL connection. For Tableau Desktop and Tableau Server, you should use a Tableau Data source Customization (TDC) file to ensure Tableau passes this parameter to the driver.
Note
Microsoft does not provide support for third-party tools. While we have tested that this solution works with Tableau Desktop 2020.3.2, you should use this workaround on your own capacity.
- To learn how to make global customizations with a TDC file on Tableau Desktop, refer to Tableau Desktop documentation.
- To learn how to make global customizations with a TDC file on Tableau Server, refer to Using a .TDC File with Tableau Server.
The example below shows a Tableau TDC file that passes the FMTONLY=YES parameter to the SQL connection string:
<connection-customization class='azure_sql_dw' enabled='true' version='18.1'>
<vendor name='azure_sql_dw' />
<driver name='azure_sql_dw' />
<customizations>
<customization name='odbc-connect-string-extras' value='UseFMTONLY=yes' />
</customizations>
</connection-customization>
For more details about using TDC files, contact Tableau support.