Querying Data in a Serverless SQL Pool in Azure Synapse

jhs_learner · January 7, 2022, 9:55pm

Hi all,

I successfully connected from KNIME 4.4.2 to serverless SQL pool in Azure Synapse. When trying to query data with the DB Query Reader node, however, I get the error message that the T-SQL function @@TRANCOUNT is not found.

And indeed the T-SQL reference says that this function is not supported by serverless SQL pool in Azure Synapse.

Is there any workaround or solution for this issue?

Many thanks for your help,
Jan

jhs_learner · January 8, 2022, 12:43pm

Maybe I’d add: this error occurs though I’m not actually using @@TRANCOUNT in my SQL statement. It appears the DB Query Reader uses this function in the background?!

Daniel_Weikert · January 8, 2022, 3:55pm

Hi
I am not a SQL expert but just in case you can rewrite your statement then you could try to use
DB Reader Node (instead of DB Query Reader) and write the SQL statement directly there.
br

jhs_learner · January 9, 2022, 1:17pm

Hi Daniel,
thanks for your reply. Unfortunately the same error occurs for the DB Reader Node.
BR, Jan

tobias.koetter · January 10, 2022, 8:47am

Hi Jan,
can you please enable statement logging to see what statement causes the error. To do so open the DB Connector node and go to the Advanced tab. Scroll down to the JDBC logger section and enable it. Once this is done execute the DB Query Reader node again and check the log file vie View-> Open KNIME log.
Can you also tell us which JDBC driver (jTDS or Microsoft driver) and version you are using.
Bye
Tobias

jhs_learner · January 10, 2022, 10:59am

Hi Tobias,
I’m connecting via JDBC driver for MS SQL Server Version 9.2 and using Azure ActiveDirectory authentification. Please see below for the KNIME log.
Many thanks,
Jan

2022-01-10 10:56:01,311 : WARN  : KNIME-Worker-9-SQL Server AD 0:55 :  : DBDataPortObjectSpec : DB Table Selector : 0:2 : The query was canceled.
2022-01-10 10:56:10,394 : WARN  : KNIME-Worker-8-DB Query Reader 0:6 :  : TransactionManagingWrapper : DB Query Reader : 0:6 : A local transaction has not been closed!
2022-01-10 10:56:10,398 : WARN  : KNIME-Worker-8-DB Query Reader 0:6 :  : TransactionManagingWrapper : DB Query Reader : 0:6 : An error occurred during the closure of the unclosed local transaction.
com.microsoft.sqlserver.jdbc.SQLServerException:  '@@TRANCOUNT' is not supported.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:283)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:129)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:26)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:3316)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3274)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:3320)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(SQLServerConnection.java:3516)
	at org.knime.database.connection.impl.managing.transaction.BroadTransactionManagingConnectionWrapper$ShortTransaction.close(BroadTransactionManagingConnectionWrapper.java:231)
	at org.knime.database.connection.impl.managing.transaction.TransactionManagingConnectionWrapper.close(TransactionManagingConnectionWrapper.java:204)
	at org.knime.database.agent.reader.impl.DefaultDBReader.read(DefaultDBReader.java:151)
	at org.knime.database.node.io.reader.query.DBQueryReaderNodeModel.execute(DBQueryReaderNodeModel.java:165)
	at org.knime.core.node.NodeModel.executeModel(NodeModel.java:549)
	at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1267)
	at org.knime.core.node.Node.execute(Node.java:1041)
	at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:559)
	at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
	at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:201)
	at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:117)
	at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:365)
	at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:219)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
	at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
2022-01-10 10:56:10,399 : ERROR : KNIME-Worker-8-DB Query Reader 0:6 :  : Node : DB Query Reader : 0:6 : Execute failed:  '@@TRANCOUNT' is not supported.
com.microsoft.sqlserver.jdbc.SQLServerException:  '@@TRANCOUNT' is not supported.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:283)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:129)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:26)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:3316)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3274)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:3320)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(SQLServerConnection.java:3516)
	at org.knime.database.connection.impl.managing.transaction.BroadTransactionManagingConnectionWrapper$ShortTransaction.close(BroadTransactionManagingConnectionWrapper.java:231)
	at org.knime.database.agent.reader.impl.DefaultDBReader.read(DefaultDBReader.java:151)
	at org.knime.database.node.io.reader.query.DBQueryReaderNodeModel.execute(DBQueryReaderNodeModel.java:165)
	at org.knime.core.node.NodeModel.executeModel(NodeModel.java:549)
	at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1267)
	at org.knime.core.node.Node.execute(Node.java:1041)
	at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:559)
	at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
	at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:201)
	at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:117)
	at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:365)
	at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:219)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
	at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)

tobias.koetter · January 11, 2022, 8:18am

Hi Jan,
thanks for the information. It seems as if the serverless SQL pool does not support transactions as the standard SQL server does. So when KNIME closes a transaction the driver executes a command that uses the @@TRANCOUNT function. Can you please execute this command
SET IMPLICIT_TRANSACTIONS OFF
in a DB SQL Executor node right after the DB Connector node to enable auto commit which might prevent the driver from calling the @@TRANCOUNT function.
I also created a ticket to allow disabling of transactions in the Microsoft SQL Server Connector node (internal reference AP-18202).
Bye
Tobias

jhs_learner · January 11, 2022, 4:54pm

Hi Tobias,
thanks for your reply and ticket creation. Unfortunately SET IMPLICIT_TRANSACTIONS OFF didn’t have any effect. I still get the same error message. I’ll try to do the database connection in R and integrate the results in the KNIME workflow via R nodes.
Best,
Jan

FilipBeerwulf · March 29, 2022, 1:00pm

Hi guys,

I am having the same issue. He was switched to serverless recently, since then I can run any queries using KNIME, I am forced to work with files only.

Anybody else has any idea how to avoid the problem?

Thanks and regards,
Filip

jhs_learner · March 29, 2022, 2:37pm

Hi Filip,

I finally worked around this issue by connecting to the database via R and integrating the respective R scripts with R nodes in the KNIME workflow.

I guess as long as the feature to disable transactions in the native KNIME database nodes isn’t implemented there’s no better solution (maybe using python instead of R, but that’s another discussion )

Best,
Jan

FilipBeerwulf · March 30, 2022, 12:37pm

Hi Jan, sad to hear, since I only use SQL.

tobias.koetter · May 13, 2022, 3:34pm

Hello everyone,

I have good news for you. With the next release of the KNIME Analytics Platform, which is planned for summer, the Microsoft SQL Server Connector will support disabling of transactions. Once disabled you can use it to connect to a serverless SQL pool in Azure Synapse. After the release you can disable transactions by unselecting the Enable flag in the Transaction section of the Advanced tab as shown here:

We will also add a section about this to the database documentation once it is released.

It would be great if you could give it a try and provide feedback. To do so download the nightly build and disable transactions in the Advanced tab.

Bye
Tobias

jhs_learner · May 15, 2022, 8:33am

Hi Tobias,

thanks for the update. This is good news.
Unfortunately I’m not able to test since due to organisational changes I’m not having access to a the SQL Serverless pool anymore

But anyway, really appreciate how KNIME team response to user feedback.

Best, Jan

system · August 13, 2022, 8:33am

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.