I have received this error many times on different servers in my careers. There is no single fix for this Error. Server: Msg 7391, Level 16, State 1, Line 1 can happen due to many reasons. I have used various of this reasons with few of my servers. Please refer them and try them one by one. One of them should be applicable to your problem.
You may receive a 7391 error message in SQLOLEDB when you run a distributed transaction against a linked server after you install Windows XP Service Pack 2 or Windows XP Tablet PC Edition 200. View Article.
INFO: Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall. View Article.
Fix/Workaround/Solution:
Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 SP4. View Article.
You receive a “Server: Msg 7391” error message when you try to perform a distributed transaction by using a Microsoft OLE DB Provider for DB2 linked server in SQL Server. View Article.
Once the error happened when I was updating local table from link server table columns. I was able to resolve the error by inserting linked server table columns to local temporary table and update local table from local temporary table.
Reference : Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Update stmt. fails within a transaction over linked server
(A-B)
OLE DB provider “SQLNCLI” for linked server “B” returned message “No transaction is active.”.
Msg 7391, Level 16, State 2, Procedure sp_abc
The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “B” was unable to begin a distributed transaction.
But this error occurs only one-way.
On Server A:
/- ERROR (above)
begin tran
update B.dbmaint..abc
set x=5 where y = 4
commit tran
-/
However, it works without a transaction
/-WORKS
update B.dbmaint..abc
set x=5 where y = 4
-/
On server B:
/- WORKS
begin tran
update A.dbmaint..abc
set x=5 where y = 4
commit tran
-/
Both servers run sql2005. Don’t know about the service packs though.
Thanks
Hi,
I received following error when using begin transaction in stored procedure insrting record on linked server.
Without begin the process works fine.
OLE DB provider “SQLNCLI” for linked server “LINK_TEST” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 50000, Level 16, State 1, Procedure usp_DataTrf, Line 114
The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “LINK_TEST” was unable to begin a distributed transaction.
Stored Procedure Name – usp_DataTrf
Linked server name – LINK_TEST
SQL server 2005 enterprise edition
Windows Server 2003
Doctor Dave,
This error 7391 is really generic and isn´t easy to find the source.
In my enviroment Win2k3 R2 + SQL20005 SP2 and Linux Red Hat Enterprise + Oracle 9.2.0.1 show me this error after created linked server like KB documents. I change regedit, update MDAC, ports, disable firewall, enable MSDTC with network services account. But I found the in METALINK that this error begins because oracle 9.1.0.1 had a bug and is must necessary give a new version of Oracle Services for Microsoft Transaction Server. After that, the linked server worked very well.
Thanks for help.
Marcos Cavalcanti, João Pessoa city, Paraiba state, Brazil.
I was gettig this error since yesterday and tried every possible way to solve it. It started occuring when I installed SP2 on my win2003 server.
Ultimately I rebooted the server where I was inserting data in temp table from stored procedure of one of the linked server and now no issues at all
Thanks
Updating Local table by linked server query can be done thru OPENQUERY.
INSERT INTO #TempTable SELECT * FROM Openquery([LinkServer],’Exec master..PROC)
Another option is to use OPENROWSET
I’m encountering this when calling a stored procedure on a linked server. DTC and whatnot is all configured properly as I can exec my call without a problem from management studio.
declare @recid as varchar(15)
exec mylinkedserver.master.dbo.xp_gmnewrecid ‘SYSTEM’, @recid output
print @recid
That works fine.
When I try to exec the same sp code _from within a trigger_, I get
OLE DB provider “SLQNCLI10” for linked server “mylinkedserver” returned message “No Transaction is active.”.
What’s going on?? Is there a setting I need to flip to allow triggers to do this or.. ??
Any thoughts appreciated!