The driver supports distributed transactions through JTA.

Note: Distributed transactions through JTA are not supported for Microsoft Azure, Microsoft Azure Synapse Analytics, or Microsoft Analytics Platform System.

To use JDBC distributed transactions through JTA, use the following procedure to install Microsoft SQL Server JDBC XA procedures. Repeat this procedure for any Microsoft SQL Server installation that uses distributed transactions.

If you have multiple instances of Microsoft SQL Server on the same machine, you can edit the .sql script file with a text editor to specify a fully qualified path to the sqljdbc.dll file for a particular instance. You will run one of two available script files depending on the version of SQL Server you are using.

  • For SQL Server 2008 or higher, the instjdbc.sql script should be used.
  • For SQL Server 2005, the instjdbc_2005.sql script should be used.

For example, if you want to install XA Procedures for an instance named "MSSQL.2," modify the .sql script file as shown and run it as described in the following procedure. 

/*
**  add references for the stored procedures
*/
print 'creating JDBC XA procedures'
go
sp_addextendedproc 'xp_jdbc_open', 
   'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn\sqljdbc.dll'
go
sp_addextendedproc 'xp_jdbc_open2', 
   'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn\sqljdbc.dll'
go
sp_addextendedproc 'xp_jdbc_close', 
   'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn\sqljdbc.dll'
go
sp_addextendedproc 'xp_jdbc_close2', 
   'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn\sqljdbc.dll'
go
sp_addextendedproc 'xp_jdbc_start', 
   'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn\sqljdbc.dll'
...

To install stored procedures for JTA:

  1. Stop the Microsoft SQL Server instance.
  2. Copy the appropriate 32-bit or 64-bit sqljdbc.dll file to the SQL_Server_Root/bin directory of the Microsoft SQL Server database server:
    sqljdbc.dll Version File Location
    32‑bit install_dir/SQLServer JTA/32‑bit
    64‑bit Itanium install_dir/SQLServer JTA/64‑bit
    64‑bit AMD64 and Intel EM64T install_dir/SQLServer JTA/x64‑bit

    where:

    install_dir is your product installation directory.

    SQL_Server_Root is your Microsoft SQL Server installation directory.

  3. Start the Microsoft SQL Server instance.
  4. From the database server, use the ISQL utility to run the .sql script. As a precaution, have your system administrator back up the master database before running the script.

    At a command prompt, run the script. For example:

    ISQL -Usa -Psa_password -Sserver_name -ilocation\instjdbc.sql

    where:

    sa_password is the password of the system administrator.

    server_name is the name of the server on which the Microsoft SQL Server database resides.

    location is the full path to instjdbc.sql. This script is located in the install_dir/SQLServer JTA directory, where install_dir is your product installation directory.

  5. The script generates many messages. In general, these messages can be ignored; however, the system administrator should scan the output for any messages that may indicate an execution error. The last message should indicate that the script ran successfully. The script fails when there is insufficient space available in the master database to store the JDBC XA procedures or to log changes to existing procedures.