Note: The SQL Server driver does not support distributed transactions through JTA for the Microsoft Windows Azure SQL Database.

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 instjdbc.sql script with a text editor to specify a fully qualified path to the sqljdbc.dll file for a particular instance. For example, if you want to install XA Procedures for an instance named "MSSQL.2," modify the instjdbc.sql script 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: For Microsoft SQL Server 2005 and higher, 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. For Microsoft SQL Server 2000, you can use the SQL Server Enterprise Manager. 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 instjdbc.sql script. As a precaution, have your system administrator back up the master database before running instjdbc.sql.

    At a command prompt, run instjdbc.sql:

    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 instjdbc.sql 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 instjdbc.sql 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.