Msdtc Linked Server Error
Contents |
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow was unable to begin a distributed transaction linked server the company Business Learn more about hiring developers or posting ads with us Database Administrators ole db provider sqlncli10 for linked server returned message no transaction is active Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who
Msg 7391, Level 16, State 2
wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers
The Transaction Manager Has Disabled Its Support For Remote/network Transactions
are voted up and rise to the top How can I enable distributed transactions for a linked server? up vote 3 down vote favorite I have a SQL Server 2012 instance with a linked server pointing to a db2 database on an IBM Iseries system using the System i Access ODBC Driver. I'm trying to enable distributed transactions. I believe this provider does support them as per IBM documentation (unless I'm misunderstanding that oraoledb.oracle for linked server was unable to begin a distributed transaction link). I'm running the following query: BEGIN TRANSACTION UPDATE LINKEDSERVER.LINKEDDATABASE.SCHEMANAME.TABLENAME SET COLUMN = VALUE SELECT CAST('qwerty' as integer) -- we want an error here so the transaction fails COMMIT TRANSACTION I get the following error: OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[IBM][System i Access ODBC Driver]Enlist with DTC phase failed. 2". Msg 7391, Level 16, State 2, Line 2 The operation could not be performed because OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" was unable to begin a distributed transaction. All the research I've done on this error has met with people wanting to disable distributed transactions, not get them working. I've already set "Enable Promotion of Distributed Transactions" to true on the linked server properties. Any ideas how I can get this working? EDIT: More information/update. I was about ready to give up on this for now and wait for help, when I noticed this in the windows logs: A caller has attempted to register an XA resource while XA transactions are disabled. I found this link which told me how to modify the settings for the MSDTC. After playing with the settings and restarting my server things started to work (kind of): (specifically, I enabled network DTC access, allow inbound/outbound/enable XA transactions). At present, the query runs and commits/rolls back as ex
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by:
Sql Server Distributed Transaction Linked Server
Distributed Transactions fail on Linked server SQL Server > SQL enable promotion of distributed transactions Server Database Engine Question 0 Sign in to vote We get the below error a nested transaction was required because the xact_abort option was set to off. while performing a distributed transaction on linked server. We have several linked servers configured in the source server and all of them succeed with the distributed http://dba.stackexchange.com/questions/60858/how-can-i-enable-distributed-transactions-for-a-linked-server transaction except on one. We did all the basic troubleshooting and moreover the distributed transactions work fine if we use a remote server instead. Need your expert guidance in resolving this issue Error: OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" returned message "No transaction is https://social.msdn.microsoft.com/Forums/sqlserver/en-US/523116d4-4c8e-43a8-abf4-e705ffaa68c8/distributed-transactions-fail-on-linked-server?forum=sqldatabaseengine active.". Msg 7391, Level 16, State 2, Line 3 The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction. Test code: begin distributed transaction select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects ROLLBACK Source server : Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64) Nov 12 2008 12:10:04 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0
here for a quick overview of the site Help Center Detailed answers to any questions you might have http://stackoverflow.com/questions/7473508/unable-to-begin-a-distributed-transaction Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us http://www.sqldbadiaries.com/2014/09/24/no-transaction-is-active-message-when-accessing-linked-server/ Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like linked server you, helping each other. Join them; it only takes a minute: Sign up Unable to begin a distributed transaction up vote 67 down vote favorite 16 i'm trying to run SQL against a linked server, but i get the errors. BEGIN DISTRIBUTED TRANSACTION SELECT TOP 1 * FROM Sessions OLE DB provider "SQLNCLI" for linked server was unable to "ASILIVE" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 3 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ASILIVE" was unable to begin a distributed transaction. There are two errors returned by the provider: Error #1: Number: $80040E14 Source: Microsoft OLE DB Provider for SQL Server Description: OLE DB provider "SQLNCLI" for linked server "ASILIVE" returned message "No transaction is active.". HelpFile: HelpContext: $00000000 SQLState: 01000 NativeError: 7412 Error #2 Number: $80040E14 Source: Microsoft OLE DB Provider for SQL Server Description: The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ASILIVE" was unable to begin a distributed transaction. HelpFile: HelpContext: $00000000 SQLState: 42000 NativeError: 7391 How do i get Microsoft to favor functionality over security? Or, at the very least, how can i get two SQL Severs to talk to each other? Related questions The operation could not be performed because OLE DB provider “SQLNCLI10”... (*linked server name is (null)) Distributed
Servers. They had quite a few Linked Servers setup. After migration any Distributed Transaction like the one below across the linked servers would fail immediately. begin distributed tran
select * from RemoteServer.DBName.dbo.TableName
commit tran Error message OLE DB provider "SQLNCLI11" for linked server "linkedservername" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "linkedservername" was unable to begin a distributed transaction.
The first place I checked for problems was the Component Services (run -> dcomcnfg). The options in the Local DTC Properties were correctly set as seen in this screenshot. Restarting the "Distributed Transaction Coordinator (MSDTC)" service didn't help either. The next step was to look for possible error messages in the Event Log. In the Application Event Log, the following error message was logged. The local MS DTC detected that the MS DTC on ServerName has the same unique identity as the local MS DTC.
This means that the two MS DTC will not be able to communicate with each other.
This problem typically occurs if one of the systems were cloned using unsupported cloning tools.
MS DTC requires that the systems be cloned using supported cloning tools such as SYSPREP. Running 'msdtc -uninstall' and then 'msdtc -install' from the command prompt will fix the problem.
Note: Running 'msdtc -uninstall' will result in the system losing all MS DTC configuration information. The error message in bold indicates that the Unique Identity for the MS DTC (SID) was same on both the local and the destination servers. How is this possible? While the new servers were being built they had syspreped servers. Hence the configuration of MS DTC was also propagated to all the servers where the same image was used. Now that we knew the root cause, the resolution