skip to main content
DataDirect Connect Drivers : Microsoft SQL Server Driver : JTA Support: Installing Stored Procedures
  
JTA Support: Installing Stored Procedures
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.