FIX: Error message when you run a distributed query in SQL Server 2005 or SQL Server 2008: "OLE DB provider 'SQLNCLI' for linked server '' returned message 'No transaction is active'"

Solution 1
Symptoms:

When you run a distributed query that uses a linked server in Microsoft SQL Server 2005 or in Microsoft SQL Server 2008, the execution fails. Additionally, you receive the following error message:
OLE DB provider "SQLNCLI" for linked server "Linked Server" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ServerName" was unable to begin a distributed transaction.
The transaction active in this session has been committed or aborted by another session.
Location: wrkspace.cpp:440
Expression: !PSess ()->FHasWorkspaceRef ()
SPID: SPID
Process ID: ProcessID
Msg 3985, Level 16, State 2, Line 1
An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Additionally, a minidump file is generated in the SQL Server 2005 log folder or in the SQL Server 2008 log folder.

Download Update:
http://support.microsoft.com/kb/954950


Solution 2
Step 1:

Test Code :
 
 
 
begin distributed transaction
select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects
ROLLBACK
 
 
 


If your distributed transactions fail because of network connectivity problems, this failure might be related to the configuration of the DTCs that are participating in the transactions. Check each DTC and make sure that Network MS DTC Access is enabled. You can use the following procedure to accomplish this task.


  1. Open the Component Services snap-in.
    To open Component Services, click Start. In the search box, type dcomcnfg, and then press ENTER.
  2. Expand the console tree to locate the DTC (for example, Local DTC) for which you want to enable Network MS DTC Access.
  3. On the Action menu, click Properties.
  4. Click the Security tab and make the following changes:
    • In Security Settings, select the Network DTC Access check box.
    • In Transaction Manager Communication, select the Allow Inbound and Allow Outbound check boxes.
  5. Click OK.
 


Step 2:
exec sp_serveroption 'Linked Server Name','remote proc transaction promotion','false'

Comments

Unknown said…
Thank's, you solved a problem for me with this, the...
exec sp_serveroption 'Linked Server Name','remote proc transaction promotion','false'
was the key :)

Big thank's from Sweden
Unknown said…
Thank's,

You solved a problem for me with this line...
exec sp_serveroption 'Linked Server Name','remote proc transaction promotion','false'
was the kye

Best regards from Sweden