The operations performed on SQL Server using the SQL adapter (through BizTalk Server, the WCF service model, or the WCF channel model) can be performed within a transaction scope. If the client program has more than one transactional resource as part of the same transaction, the transaction gets elevated to an MSDTC transaction. To enable the adapter to perform operations within the scope of an MSDTC transaction, you must configure MSDTC both on the computer running the SQL adapter and SQL Server. Also, you must add MSDTC to the exceptions list of Windows Firewall. This section provides information about how to perform these tasks on computers running the adapter client and SQL Server.
 Note |
|---|
| Performing operations on SQL Server using BizTalk Server always involves two resources—the adapter connecting to SQL Server and the BizTalk Message Box residing on SQL Server. Hence, all operations performed using BizTalk Server are performed within the scope of an MSDTC transaction. So, to use the SQL adapter with BizTalk Server, you must always enable MSDTC. |
| Note |
|---|
| For operations where the adapter client does not write any data to the SQL Server database, such as a Select operation, you might not want the additional overhead of performing the operations inside a transaction. In such cases, you can configure the SQL adapter to perform operations without a transactional context by setting the UseAmbientTransaction binding property to false. For more information about the binding property, see Working with BizTalk Adapter for SQL Server Binding Properties. In such cases, you do not need to configure MSDTC as well. |
- To enable firewall exceptions for MSDTC on computers running Windows Vista or Windows Server 2008, refer to http://go.microsoft.com/fwlink/?LinkId=138685.
- To enable firewall exceptions for MSDTC on computers running Windows XP or Windows Server 2003, do the following:
- Open the Windows Firewall dialog box.
- On a computer running Windows XP, click Start, click Control Panel, and then double-click Windows Firewall.
- On a computer running Windows Server 2003, click Start, click Control Panel, and then click Windows Firewall.
- In the Windows Firewall dialog box, click the Exceptions tab, and then click Add Program.
- In the Add a Program dialog box, click Browse.
- In the Browse dialog box, browse to the location of msdtc.exe.
- On a computer running Windows XP, browse to <system drive>:\WINDOWS\system32.
- On a computer running Windows Server 2003, browse to <system drive>:\WINNT\system32.
- In the Add a Program dialog box, click OK.
- In the Windows Firewall dialog box, click OK.
