Databases
StackOverflow2013
Postsdbo
POUnable to begin a distributed transaction
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUnable to begin a distributed transaction
    primarykey
    data
    text
    <p>i'm trying to run SQL against a linked server, but i get the errors.</p> <pre><code>BEGIN DISTRIBUTED TRANSACTION SELECT TOP 1 * FROM Sessions OLE DB provider "SQLNCLI" for linked server "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. </code></pre> <p>There are two errors returned by the provider:</p> <p><strong>Error #1:</strong></p> <pre><code>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 </code></pre> <p><strong>Error #2</strong></p> <pre><code>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 </code></pre> <p>How do i get Microsoft to favor functionality over security?</p> <p>Or, at the very least, how can i get two SQL Severs to talk to each other?</p> <h2>Related questions</h2> <ul> <li><a href="https://stackoverflow.com/questions/6407447/the-operation-could-not-be-performed-because-ole-db-provider-sqlncli10-for-link">The operation could not be performed because OLE DB provider “SQLNCLI10”...</a> (*linked server name is <code>(null)</code>)</li> <li><a href="https://stackoverflow.com/questions/6999906/distributed-transaction-error">Distributed transaction error?</a> (<em>using Oracle provider</em>)</li> <li><a href="https://stackoverflow.com/questions/1012147/unable-to-enlist-in-a-distributed-transaction-with-nhibernate">Unable to enlist in a distributed transaction with NHibernate</a> (<em>using Hibernate</em>)</li> <li><a href="https://stackoverflow.com/questions/6608191/error-using-distributed-transaction-in-sql-server-2008-r2">Error using distributed transaction in SQL Server 2008 R2</a> (<em>SQL Server 2008 R2, no answer</em>)</li> <li><a href="https://stackoverflow.com/questions/4205484/distributed-transaction-error-only-through-code">Distributed Transaction Error Only Through Code</a> (<em>caused by connection pooling</em>)</li> <li><a href="https://stackoverflow.com/questions/7298565/error-performing-distributed-transaction-coordinator-in-linked-server">Error performing distributed transaction coordinator in linked server</a> (<em>SQL Server 2008, no answer</em>)</li> <li><a href="https://stackoverflow.com/questions/6999906/distributed-transaction-error">Distributed transaction error?</a> (<em>no accepted answer; only answer doesn't help</em>)</li> <li><a href="https://stackoverflow.com/questions/3033838/how-to-insert-into-remote-table-using-linked-server-withint-transaction">How to Insert into remote table using Linked server withint Transaction?</a> (<em>accepted answer doesn't resolve</em>)</li> </ul> <hr> <p>What i <em>have</em> done is irrelevant, but i'll post it anyway.</p> <ol> <li><p>Ensure <code>Distributed Transaction Coordinator</code> service is running on both machies:</p> <p><img src="https://i.stack.imgur.com/l8iss.png" alt="enter image description here"></p> <p><img src="https://i.stack.imgur.com/yx3Pm.png" alt="enter image description here"></p></li> <li><p>Disable all MSDTC security on both machines:</p> <p><a href="https://i.stack.imgur.com/7iJ7X.png" rel="noreferrer"><img src="https://i.stack.imgur.com/7iJ7X.png" alt="enter image description here"></a></p> <p><img src="https://i.stack.imgur.com/SFTZn.png" alt="enter image description here"></p></li> <li><p>Turn on random options on the linked server:</p></li> </ol> <p><a href="https://i.stack.imgur.com/M22Wa.png" rel="noreferrer"><img src="https://i.stack.imgur.com/M22Wa.png" alt="enter image description here"></a></p> <ol start="4"> <li><p>Cursed and swore.</p></li> <li><p>Smashed things.</p></li> <li><p>Checked that a <code>SELECT</code> can use the <em>linked server</em>:</p> <pre><code> SELECT * FROM ASILive.CustomerManagementSystem.dbo.Users .... (763 row(s) affected) </code></pre></li> <li><p><a href="http://www.sqlservercentral.com/Forums/Topic241855-5-2.aspx" rel="noreferrer">Checked that client server can <code>ping</code> the remote server</a>:</p> <pre><code> C:\Documents and Settings\avatar&gt;ping asicmstest.contoso.com Pinging asicmstest.contoso.com [10.0.0.40] with 32 bytes of data: Reply from 10.0.0.40: bytes=32 time&lt;1ms TTL=128 Reply from 10.0.0.40: bytes=32 time&lt;1ms TTL=128 Reply from 10.0.0.40: bytes=32 time&lt;1ms TTL=128 Reply from 10.0.0.40: bytes=32 time&lt;1ms TTL=128 Ping statistics for 10.0.0.40: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms </code></pre></li> <li><p>Checked that the remote server can commnicate back, by name, to the initiating server:</p> <pre><code> C:\Documents and Settings\avatar&gt;ping asitestserver.contoso.com Pinging asitestserver.contoso.com [10.0.0.22] with 32 bytes of data: Reply from 10.0.0.22: bytes=32 time&lt;1ms TTL=128 Reply from 10.0.0.22: bytes=32 time&lt;1ms TTL=128 Reply from 10.0.0.22: bytes=32 time&lt;1ms TTL=128 Reply from 10.0.0.22: bytes=32 time&lt;1ms TTL=128 Ping statistics for 10.0.0.22: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms </code></pre></li> <li><p><a href="http://support.microsoft.com/kb/306212" rel="noreferrer">Checked that <code>@@SERVERNAME</code> matches the server name on both servers</a>:</p> <pre><code> SELECT @@SERVERNAME, SERVERPROPERTY('MachineName') ------------- ------------- ASITESTSERVER ASITESTSERVER </code></pre> <p>and</p> <pre><code> SELECT @@SERVERNAME, SERVERPROPERTY('MachineName') ---------- ---------- ASIGROBTEST ASIGROBTEST </code></pre></li> <li><p>Screamed</p></li> <li><p><a href="http://support.microsoft.com/kb/306212" rel="noreferrer">Issued <code>SET XACT_ABORT ON</code> before issuing my query</a>:</p> <pre><code>SET XACT_ABORT ON GO BEGIN DISTRIBUTED TRANSACTION SELECT TOP 1 * FROM Sessions </code></pre></li> <li><p><a href="http://support.microsoft.com/kb/306212" rel="noreferrer">Granted <code>Everyone</code> <code>Full Control</code> to</a>:</p> <pre><code>HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer </code></pre> <p>on both servers.</p></li> </ol>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USIan Boyd
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUnable to begin a distributed transaction
      1. USBrent
      1. VTFavorite
    2. VO
      singulars
      1. POUnable to begin a distributed transaction
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POUnable to begin a distributed transaction
      1. This table or related slice is empty.
      1. VTUpMod
    1. COgood to see you didn't skip the crucial "cursed and swore" step!
      singulars
      1. POUnable to begin a distributed transaction
      1. USjwl
    2. CO@joelarson Hey, sometimes people are very particular that you follow **every** step. i didn't want anyone saying that i didn't try something.
      singulars
      1. POUnable to begin a distributed transaction
      1. USIan Boyd
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload