Problems with Linked Servers on Windows 2003

Problems with Linked Servers on Windows 2003

  
If the following error occurs with linked servers please refer to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332

SYMPTOMS
When you run a distributed transaction against a linked server in SQL Server 2000 on a computer running Windows Server 2003, Enterprise Edition, you may receive the following error message:

Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.
CAUSE
The problem occurs because Microsoft Distributed Transaction Coordinator (MS DTC) is not configured for network access. 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.
If you are using MSDTC to talk to resource managers like Oracle or other XA RM, on Windows Server 2003 you need to make some additional steps to allow this scenario to work. Please refer to:

http://support.microsoft.com/?id=817066

This article will help you with enabling XA Transactions over MSDTC.
Regarding this problem, also with the same error message, sometimes after a fresh install of Windows 2003 or Windows XP Service Pack 2, some network settings and firewall mis-configurations can occur to the MSDTC.

The Microsoft article http://support.microsoft.com/?kbid=839279 describes in detail additional procedures to overcome this error.

In some ocasions, setting "No Authentication required" in the "Transaction Manager Communication" is sufficient.
Hi,

Some guidelines to test a DBLink:
1. Always use MS Query Analyser do check the connection status (instead of your OutSystems application). This way, you’ll always be focused on connectivity problems, not on application problems.
2. To check to connection in MS Query Analyser, you cannot just do a ‘SELECT * FROM {REMOTE_TABLE}’. Instead, run ‘SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;BEGIN TRANSACTION; SELECT * FROM {REMOTE_TABLE}’. This is the way OutSystems do the query.
3. If this fails, look carefully all previous post on this thread.
4. Test an UPDATE/INSERT/DELETE command on the remote table (you can always test an update like this: ‘UPDATE {REMOTE_TABLE} SET ID=ID WHERE 1=0’, that does nothing to the data). You will probably get the following SQL Error: « Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required because the XACT_ABORT option was set to OFF». The query shall be changed to: ‘SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;BEGIN TRANSACTION; SET XACT_ABORT ON ; UPDATE {REMOTE_TABLE} SET ID=ID WHERE 1=0; SET XACT_ABORT ON’.
5. If this fails, look carefully all previous post on this thread.
6. Now that your servers are connected, go to Service Studio and find all yours UPDATE, DELETE and INSERT on the remote table:
a. Before each UPDATE, DELETE and INSERT on the remote table, add an Advanced Query with SET XACT_ABORT ON.
b. After each UPDATE, DELETE and INSERT on the remote table, add an Advanced Query with SET XACT_ABORT OFF.
c. Attached is a OML with a sample of this.
7. Verify your eSpace and ignore/hide the warning «Unexpected SQL : Unexpected 'SET' in SQL statement of […]»
8. Publish and test your application