com.microsoft.sqlserver.jdbc.SQLServerException: '@@TRANCOUNT' is not supported.

[vgrimaldi848]

Hi,

I'm currently experiencing an issue when loading data from an Azure SQL Instance.

Even if the load completes, the execution fails with the following error message:

org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: '@@TRANCOUNT' is not supported.
at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:340)
at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:566)
at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:35)
at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
at org.embulk.spi.Exec.doWith(Exec.java:23)
at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:242)
at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:291)
at org.embulk.EmbulkRunner.run(EmbulkRunner.java:155)
at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:431)
at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:90)
at org.embulk.cli.Main.main(Main.java:64)
Caused by: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: '@@TRANCOUNT' is not supported.
at org.embulk.input.jdbc.AbstractJdbcInputPlugin.run(AbstractJdbcInputPlugin.java:536)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor.runInputTask(LocalExecutorPlugin.java:269)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor.access$100(LocalExecutorPlugin.java:194)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor$1.call(LocalExecutorPlugin.java:233)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor$1.call(LocalExecutorPlugin.java:230)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)
Caused by: 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.commit(SQLServerConnection.java:3496)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.commit(SQLServerConnection.java:3474)
at org.embulk.input.jdbc.JdbcInputConnection.commit(JdbcInputConnection.java:501)
at org.embulk.input.jdbc.AbstractJdbcInputPlugin.run(AbstractJdbcInputPlugin.java:533)
... 8 more

Error: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: '@@TRANCOUNT' is not supported.

This seems to be an issue with the fact that serverless pools in Azure do not support transactions.

Is it possible to disable the final check on the @@TRANCOUNT after the load?

[hiroyuki-sato]

Hello, @vgrimaldi848

It is necessary to investigate this issue deeply. But, It seems that embulk-input-sqlserver does not use @@trancount in the plugins itself.

• Have you ever tried driver_path option to specify a newer MS-SQL JDBC driver? I suppose the error was raised in JDBC Driver.
• Could you provide us reproduce configuration and data?
• What plugin and embulk version are you using?
• Does Azure SQL Instance. means this service(Azure SQL Managed Instance)?

Best regards.

[kakoni]

Hi @hiroyuki-sato and @vgrimaldi848!
I might have idea what's going on. @vgrimaldi848, are you by any chance using SQL pool in Azure Synapse Analytics?

@@TRANCOUNT...This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

(As per https://learn.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql?view=sql-server-ver16)

If you have a look at mssql-jdbc commit implementation, you see that when databaseAutoCommitMode is true (by default) then the method uses @@TRANSCOUNT.

[hiroyuki-sato]

Hello, @kakoni. Thank you for your comment.

Do you know how to change databaseAutoCommitMode to false?
Do you know if this config JDBC option?

[kakoni]

I believe one needs to do connnection.setAutoCommit(false). There is no JDBC url parameter for this.

[vgrimaldi848]

Hi @kakoni

sorry for the delay of the answer.

Yes, I'm using a server less SQL pool in MS Azure. I tried to disable the autocommit feature by adding the following to the yml file:

before_setup: SET IMPLICIT_TRANSACTIONS OFF;

but this is again triggering the same error, just at the setup stage.

I'm wondering if this is caused by a final commit at the end of the connection on SQL server, i believe this may be skipped when used as a source (like in my case).

Thanks anyway,

VG

[kamyuen]

A bit late this, but I just had the same issue but on connecting to an Azure Synapse database.
I changed the following connection properties and it resolved the issue