Solved

Event ID 4879, "The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "server-01" was unable to begin a distributed transaction".

Posted on 2010-08-31
15
4,145 Views
Last Modified: 2013-11-29
I have 2 servers running windows 2008 64-bit datacenter edition. I have enabled DTC service http://msdn.microsoft.com/en-us/library/dd327979(CS.90).aspx. But still DTC services are not talking to each other. I checked for the port 135, its opened.

SQL 2008 is installed on both servers. Linked server is working fine but DTC has issue. Error found in sql query is  

"The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "server-01" was unable to begin a distributed transaction".

Event ID : 4879
Event Viewer message:
"OLE DB provider "SQLNCLI10" for linked server "server-01" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2"

Is there any more settings to be done to enable this or is this issue is due to 64bit??
0
Comment
Question by:anuboggaram
15 Comments
 
LVL 8

Expert Comment

by:tskelly082598
ID: 33567323
0
 

Author Comment

by:anuboggaram
ID: 33585506
I could not even install this hotfix. It said "there are no sql server instances or shared features that can be updated on this computer"

I am using sql 2008 sp1.  So downloaded cumilative hotfix for sql2008 sp1, but got same error "The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "server-01" was unable to begin a distributed transaction.
0
 

Author Comment

by:anuboggaram
ID: 33596023

forgot to mention another that servers are not in domain. They are in workgroup.

I updated sql2008 with sp2 but still no luck.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 33597798
I ran into the same message in an SSIS package I was trying to continue to work on after having recently installed SS2008 and VS2008 (BIDS) on a Virtual Machine.  What I wound up doing was deleting the old OLEDB connection manager and adding a new one . . . apparently, the old one (that had been working fine before the SS/VS 2008 SP1 upgrade had been installed) was something like an SQLNCLI1 instead of the SQLNCLI10.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 33600792
Please check DTC configuration, authentication. You must use the No Authentication Required for Transaction Manager Communication. It is only working setting for workgroup computers.
Also re-check other options. You have to both Incomming and Outgoing connection.
Setting on both servers should be same.
 
 
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 500 total points
ID: 33602211
Yep, it was fixed in CU2 then apparently reappeared in CU3 for 2008...

Have a look at some of the comments in : http://connect.microsoft.com/SQLServer/feedback/details/431153/msdtc-bug-fixed-in-sql-server-2008-cu2-back-in-cu3-and-cu4

Double check your security settings, even try to test the linked server via SSMS (go to linked servers and test connection). You may have to rebuild the linked server and make very sure you add a real login for the linked server as well (can use the procedure sp_adlinkedsvrlogin if doing it via T-SQL)

Also, might be worthwhile double checking the latest and greatest native driver, download the latest via (scroll down a bit over half way) : http://www.microsoft.com/downloads/en/details.aspx?familyid=CEB4346F-657F-4D28-83F5-AAE0C5C83D52&displaylang=en


via T-SQL this is what I currently do :

EXEC sp_addlinkedserver @server='MY_LNK_SVR', @srvproduct='SQL Native', @provider='SQLNCLI10', @datasrc='MICROSOFTSMLBIZ'
GO

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'MY_LNK_SVR' , @useself = 'FALSE' , @locallogin = NULL , @rmtuser = 'My_Remote_SQL_login' , @rmtpassword = 'Some_tricky_password'  
GO

exec sp_tables_ex MY_LNK_SVR -- not needed, but is a simple check to make sure remote server is visible
GO

-- set up some remote procedure and data settings for the linked server

EXEC sp_serveroption MY_LNK_SVR, 'rpc', 'true';
EXEC sp_serveroption MY_LNK_SVR, 'rpc out', 'true';
EXEC sp_serveroption MY_LNK_SVR, 'dist', 'true';
EXEC sp_serveroption MY_LNK_SVR, 'data access', 'true';
GO

exec sp_dropserver 'MY_LNK_SVR',droplogins
GO

0
 

Accepted Solution

by:
anuboggaram earned 0 total points
ID: 33617053
Currently DTC setting is having "No Authentication Required" but still no luck.
Now i installed SQL 2008 RTM with same settings that was previously done on sql2008 sp1 and distributed transaction is working fine. No idea whats the issue in sql 2008 sp1.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33618973
Well, there was a bug reported in CU2 to have been fixed and came back in CU3 / CU4, so maybe SP1 is similarly afflicted (havent checked which cumulative updates belong where).

Might also have been a problem / difficulty with installation/upgrades ? Sometimes (and not a good answer - but not really "copping out" either) big mysteries in SQL are best resolved with a re-install... But they do need to be big mysteries to warrant such a dramatic step.
0
 

Author Comment

by:anuboggaram
ID: 33660516
Thanks Mark,
We are planning to install SQL standard 2008 R2 64-bit as solution was not found. Lets see..
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 34496720
Have no problem with the accepted answer going to anuboggaram (http:#33617053) because that is pretty much the fix - either upgrade, or retrograde, think there has since been a SP that does fix it, but the details given in http:#33602211 are correct and do explain that problem and so deserves an assist.
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 34532764
All,
 
Following an 'Objection' by mark_wills (at http://www.experts-exchange.com/Q_26721533.html) to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as recommended by the Expert.
 
At this point I am going to re-start the auto-close procedure.
 
Thank you,
 
Vee_Mod
Experts-Exchange Moderator
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
When you try to extract and to view the contents of a Microsoft Update Standalone Package (MSU) for Windows Vista, you cannot extract the files from the MSU. Here we are going to explain how to extract those hotfix details without using any third pa…
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question