SQL Server's linked server configuration allows you to execute queries or stored procedures and receive data sets from external sources by linking to other SQL Servers and database systems.
The Microsoft Distributed Transaction Coordinator (MSDTC) is used to enforce transactional consistency across these multiple systems. To ensure the feature functions correctly and doesn't fail, it is important to properly configure MSDTC and SQL Server.
If MSDTC is not configured properly, a variation of the following error (7391) will appear:
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
This error can occur if MSDTC is not allowed to access the network, which is the default setting in Windows Server 2003. To enable network access for MSDTC:
-
Open Control Panel, select Add or Remove Programs, then Add/Remove Windows Components
- Select Application Server, click Details, then enable the Network DTC Access as shown in Figure 1 below:
Figure 1
[IMAGE]
- Restart both SQL Server and MSDTC services
If the errors continue after MSDTC network access is enabled, check if MSDTC is properly configured for network access . The process is as follows:
-
Open Administrative Tools, then expand Component Services
- Drill down to Computers, My Computer
- Right-click My Computer and select Properties
- Click the MSDTC tab and choose Security Configuration
- Enable the Network DTC Access option as well both sub-options (Allow Remote Clients and Allow Remote Administration)
- Under Transaction Manager Communication, enable the following options – Allow Inbound, Allow Outbound and Enable TIP Transactions
- In the same section, select the No Authentication Required radio button
- Finally, enable the Enable XA Transactions option
The ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
screen with the above configuration should look like this:
Figure 2 (Click to enlarge)
[IMAGE]
After making the changes, reboot the SQL Server computer.
Now let's look at the setup for linked servers from SQL Server. When a linked server is set up, by default, outgoing RPC calls are disabled. Since a distributed transaction in some scenarios is executed through a remote procedure call, you may need to change the RPC Out option to True in the Properties section of the linked server setup screen, which is pictured in Figure 3 below.
Figure 3 (Click to enlarge)
[IMAGE]
Furthermore, when running INSERT…EXECUTE on a loopback linked server you may encounter Unable to begin… errors if you are using SQL Server 2005 or earlier.
A loopback linked server is a linked server pointing to itself. This occurs when two databases that resided on different SQL Servers end up on the same server, but the code continues executing as a remote linked server query. In this case, SQL Server attempts a distributed transaction, but since the linked server is not really a remote computer, it fails.
To fix this error, modify the code, removing the server part of the query and referencing the object using the three-part name. For example, change INSERT INTO MyServer.MyDatabase.dbo.MyTable to INSERT INTO MyDatabase.dbo.MyTable. The query will no longer run through MSDTC.
Configuring Microsoft Distributed Transaction Coordinator is complex and the results depend on factors like network setup, firewall configuration, security permissions and the combination and configuration of operating systems involved.
You may encounter different errors related to MSDTC than the ones discussed above. For solutions to these, visit Microsoft's Help and Support center and also check out newsgroups and forums. In addition, you can open a support call with Microsoft PSS.
ABOUT THE AUTHOR:
[IMAGE]Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.
