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'
...
Note: You can use the Microsoft SQL Server Configuration Manager tool to view Microsoft SQL Server services and determine the fully qualified path to the \Binn subdirectory of each Microsoft SQL Server instance on a machine. Using the Configuration Manager, right-click on a service and select Properties. Select the Service tab. The path is shown as a value of the Binary Path attribute. Refer to your Microsoft SQL Server documentation for details.
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.
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.
For SQL Server 2008 or higher, the instjdbc.sql script should be used.