My Oracle Support Banner

Transaction Stays Open On SQL*Server With The Parameter HS_TRANSACTION_MODEL=SINGLE_SITE_AUTOCOMMIT Using DG4MSQL (Doc ID 2045606.1)

Last updated on SEPTEMBER 28, 2021

Applies to:

Oracle Database Gateway for SQL Server - Version and later
Information in this document applies to any platform.


Using the Database Gateway for SQL*Server (DG4MSQL) a call is made to a procedure on the SQL*Server database.
The procedure on SQL Server makes insert on a SQL*server table without making any commits on the Oracle side and active transactions are left on the SQL*Server side.
The DG4MSQL init<sid>.ora has the parameter -



- and it is expected that the transaction is committed on SQL*Server, as it says in the gateway documentation -

SINGLE_SITE_AUTOCOMMIT provides read and write access to the non-Oracle
database without logging. The gateway cannot participate in distributed
Moreover, any update to the non-Oracle database is committed immediately.

Open transactions can be checked on SQL*Server using the command -

dbcc opentran

which will give output similar to -

Transaction information for the database 'Databse_Name'
Oldest active transaction :
   SPID ( the server process ID ) : 211
   UID ( User ID ) : -1
   Name : user_transaction
   LSN : ( 533303 : 241 : 1 )
   Start time : 19 December 2014 5 : 10 : 54 : 283PM
   SID : 0xfbf68d09f88c6a47a58240ce595f2d26
DBCC execution completed . If you have any error messages contact your system administrator .



To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.