e-Work Unable to Retrieve Data from Linked Server

Metastorm · October 29, 2004

The information in this article applies to product: e-Work 6.x

Issue

A customer had e-Work 6.1 installed on a server with SQL Server 2000. A second server running SQL Server 2000 was configured as a linked server on the e-Work server. A view was created in the e-Work database to a table on the linked server. Through 'Enterprise Manager', data could be retrieved from the view.

However, when attempting to populate a form in e-Work using %SelectSQL() with the view, the following was written to the Designer log:
Failed to process 'eExecuteSQL' e-Work Database Connector request. ErrorCode: '-2147217900'. Description: '\[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. \]'. Source: 'Microsoft OLE DB Provider for SQL Server'. SQL State: '01000'. NativeErrorCode: '7312' ErrorCode: '-2147217900'. Description: 'The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.'. Source: 'Microsoft OLE DB Provider for SQL Server'. SQL State: '42000'. NativeErrorCode: '7391' Procedure SelectSQL call failed. Position: 95

Also, "implicit\_transaction(aborting)" was found in the Distributed Transaction Coordinator's Transaction List (located here: Administrative Tools > Component Services > Distributed Transaction Coordinator > Transaction List).
Error 7391 is generated when running a distributed transaction against a linked server using SQL Server 2000 on Windows Server 2003, Enterprise Edition.

In the customer's case, the CID for MS DTC (Microsoft Distributed Transaction Coordinator) was the same on both servers. This will cause distributed transactions, which are used by e-Work in v6.x, to fail.

To check the CID:
1. go to HKEY\_CLASSES\_ROOT\\CID
2. Find the CID, e.g. 561e3c85-537a-486b-be90-81d1a7d0b082, whose value under the 'Description' key is 'MSDTC'.
3. If this value is the same for both servers, one must be changed. One tool that can be used for generating GUIDs can be downloaded here: Microsoft GUIDGen
In the second case, network access settings of MS DTC need to be enabled. By default, the network access settings of MS DTC are disabled on new installations of SQL Server 2000 on computers running Windows Server 2003, Enterprise Edition. See this Microsoft Knowledge Base article for more information:You Receive Error 7391 When You Run a Distributed Transaction Against a Linked Server