Shadi Cards Walima Invitations Wordings

JPA, Distributed Transactions i MS SQL Server

For the first time I face the issue distributed transactions, and is immediately in contact with MS SQL Server, which - in my opinion - is a Java-friendly medium.
itself necessitates the use of distributed transactions resulted from the requirement to export data from one database (database A - PostgreSQL) to another (base B - MS SQL). During the same export data from database A must be marked as exported successfully. Where in the record to the database B, something goes wrong, the transaction is rollbackowana and data in database A does not receive the flag "exported".
In principle, all beautifully - worse in practice. It turned out that the inclusion of support for XA transactions in MS SQL tymaga implementation of a large number of steps that I describe below:

• Enable distributed transactions on Windows is installed on SQL Server - described here.
• Configure System Distributed Transaction Coordinator (Distributed Transactions Coordinator) to start automatically at boot
  
• Download JDBC driver.
• the downloaded driver package locate the file sqljdbc_xa.dll (appropriate for the architecture) and place it in the directory structure of SQL Server.
  This file contains the implementations of stored procedures, responsible for distributed transactions.
  
• Select the operating system location of this file. To do this, add to the registry key
  HKEY_LOCAL_MACHINE \\ SOFTWARE \\ Microsoft \\ MSDTC \\ XADLL
  entry type string value (REG_SZ) being named DLL file name and a value which is the path to This file (including with the same name). In my case it was:
  name: sqljdbc_xa.dll
  value: C: \\ Program Files \\ Microsoft SQL Server \\ 100 \\ Shared \\ sqljdbc_xa.dll
• the installed instance of SQL Server installs run the SQL script needed stored procedures. The script can be of the form:
  use master
  it
  sp_addextendedproc exec 'xp_sqljdbc_xa_init', 'SQLJDBC_XA.dll'
  sp_addextendedproc exec 'xp_sqljdbc_xa_start', 'SQLJDBC_XA.dll '
  sp_addextendedproc exec 'xp_sqljdbc_xa_end', 'SQLJDBC_XA.dll'
  sp_addextendedproc exec 'xp_sqljdbc_xa_prepare', 'SQLJDBC_XA.dll'
  sp_addextendedproc exec 'xp_sqljdbc_xa_commit', 'SQLJDBC_XA.dll'
  sp_addextendedproc exec 'xp_sqljdbc_xa_rollback', 'SQLJDBC_XA.dll'
  sp_addextendedproc exec 'xp_sqljdbc_xa_forget', 'SQLJDBC_XA.dll'
  sp_addextendedproc exec 'xp_sqljdbc_xa_recover', 'SQLJDBC_XA.dll'
  sp_addextendedproc exec 'xp_sqljdbc_xa_rollback_ex', 'SQLJDBC_XA.dll'
  exec sp_addextendedproc 'xp_sqljdbc_xa_forget_ex', 'SQLJDBC_XA.dll'
  exec sp_addextendedproc 'xp_sqljdbc_xa_prepare_ex', 'SQLJDBC_XA.dll'
  go
  
  grant execute on xp_sqljdbc_xa_init to public
  grant execute on xp_sqljdbc_xa_start to public
  grant execute on xp_sqljdbc_xa_end to public
  grant execute on xp_sqljdbc_xa_prepare to public
  grant execute on xp_sqljdbc_xa_commit to public
  GRANT EXECUTE ON xp_sqljdbc_xa_rollback is public
  grant execute on xp_sqljdbc_xa_recover is public
  grant execute on xp_sqljdbc_xa_forget is public
  grant execute on xp_sqljdbc_xa_rollback_ex is public
  grant execute on xp_sqljdbc_xa_forget_ex is public
  grant execute on xp_sqljdbc_xa_prepare_ex is public
  it
  
  
• Configuring Connection Pool:
  DataSource classname = com.microsoft.sqlserver.jdbc.SQLServerXADataSource
  Resource type = javax.sql.XADataSource
  

Remember to choose the appropriate version of the JDBC driver:

• sqljdbc.jar file for Java 5
• sqljdbc4.jar-file for the Java 6

If you have problems connecting to SQL Server checks the article Six Possible Solutions For JDBC - SQL Server Connection Problems .

Aha! And watch out for SQL Server 2008 (do not know if earlier versions too) - standard Only option is enabled a user logs on to Windows Authentication, so you can not log
through JDBC's database. Only manually activate this option helps;)