SQL Server xa_open string

Restriction: This topic applies only when the Enterprise Server feature is enabled.

Restriction: This topic applies to Windows environments only.

To complete the definition of a SQL Server XAR, add the following values in the Open string field:

DSN=odbc_dsn[,USRPASS=userid.password][,TM=tmname]
   [,RMRECOVERYGUID=guid][,TIMEOUT=timeoutvalue]
   [,LOCALTX={T|F}]
   [,ISOLEVEL={CR|RR|SS|SZ|UR}][,ESLOGGING=0001]

odbc_dsn: The ODBC Data Source Name used to connect to the data source as defined within the Windows ODBC Data Source Administrator utility.¹
userid: The user ID required to connect to the data source.¹
password: The password required to connect the user ID to the data source.¹
tmname: The transaction manager name used for diagnostics by the Microsoft Distributed Transaction Coordinator (MSDTC). We recommended that you specify the transaction manager name as a string that uniquely identifies the host machine and enterprise server instance. The tmname value is restricted to 40 characters. If not specified, the TM value defaults to Micro Focus Enterprise Server.
guid: A 36-character GUID used to optimize recovery processing.
timeoutvalue: The amount of time in milliseconds that MSDTC waits for transactions to complete. If not specified, the default is 0, meaning no time limit.
LOCALTX, ISOLEVEL, and ESLOGGING: See SQL xa_open string Configuration Options for details.

¹ Alternatively, you can specify the DSN, user ID, and password by manually coding them into the esmssql.cbl source. This can be helpful if you want to ease security. To do this, search for CUSTOMIZE within esmssql.cbl to see where to apply the required changes.

Note:

If you specify any parameter other than those listed here, the switch module returns an error to Enterprise Server.
The SQL Server switch module does not accept the DTCSERVER parameter. Consequently, it is not possible to define the resource manager to identify a specific machine to act as the transaction coordinator in the XAR. However, using Windows Component Services, you can configure a remote machine to act as the transaction coordinator. To do this, on your local machine, change the default MSDTC properties to use a remote coordinator, specifying the computer name of the remote machine as the remote coordinator host name. See your Windows documentation for complete instructions.

Configuring MSDTC and SQL Server to act as an XA resource manager

Start the Windows Component Services Administrative tool.
Open the Properties page for Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC.
On the Security tab, check Enable XA Transactions.

Configuring a remote machine to act as transaction coordinator

Using a remote machine as a transaction coordinator could improve performance because Enterprise Server and SQL Server are not required to compete for resources on the same machine. To configure a remote machine to act as a transaction coordinator:

On the host machine, start the Windows Component Services Administrative tool.
Open the Properties page for Component Services > Computers > My Computer.
On the MSDTC tab, uncheck Use local coordinator.
In the Remote coordinator host name field, type the computer name or IP address of the machine you want to act as the transaction coordinator, or click Select to select a machine.