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,124 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
 
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
Shouldn't all users have the same email signature?

You wouldn't let your users design their own business cards, would you? So, why do you let them design their own email signatures? Think of the damage they could be doing to your brand reputation! Choose the easy way to manage set up and add email signatures for all users.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

NTFS file system has been developed by Microsoft that is widely used by Windows NT operating system and its advanced versions. It is the mostly used over FAT file system as it provides superior features like reliability, security, storage, efficienc…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now