Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [distributed-transactions]

A distributed transaction is a unit of work in which two or more network hosts are involved. Usually hosts provide transactional resources while the transaction manager is responsible for creating and managing a global transaction that encompasses all the operations. Distributed transactions must have all four ACID (atomicity, consistency, isolation, durability) properties, where atomicity guarantees all-or-nothing outcomes for the unit of work.

2
votes
1answer
221 views

Auto-commit UPDATE Transaction On Linked Server

I would like to know more about what happens behind the scenes with an auto-commit transaction when performing a cross-server query on a linked server. I naïvely think that when executing an auto-...
1
vote
0answers
27 views

new transaction started on exception

We are running sql server 2016 enterprise edition. We have a linked server configured to db2. We do a distributed transaction between mssql 2016 and db2 using MS DTC. SQL Server controls the ...
5
votes
1answer
4k views

Insert into linked server fails with “The transaction manager has disabled its support for remote/network transactions”

Two servers: sq009 and sq202, both running SQL Server 2012 SP3. sq202 is the SQL Server hosting the DB for our ERP system, Sage 500. sq009 is the SQL Server hosting the DB for our IBM Maximo instance....
1
vote
1answer
103 views

Microsoft Distributed Transactions from Informix with Linux

I need to create an extraction job from an Informix database, the requirement is that I need to enable transactions in SSIS. All of the MSDTC settings of the target machine and the SQL Server database ...
0
votes
1answer
5k views

Unable to begin a Distributed transaction

Can someone help me to troubleshoot the below error? I am getting the error executing the below code. CREATE TABLE #Drive ( drive char(1) Primary Key, freespace int null ) Insert into #...
7
votes
1answer
917 views

Where do Linked Server Queries get executed?

I have two instances ServerA and ServerB, and I have created a linked server in ServerA for ServerB as Linksrv_B. I can execute a query on ServerA using the four part naming convention: SELECT * ...
1
vote
0answers
1k views

Oracle after run procedure returned ORA-02046

I made small changes in a procedure to calculate value for statistic monitoring. In procedure calculate_1 on remote DB, I added check null or no null - PARAMETER_ID OPEN CALCULATE_PARAMETER; ...
1
vote
0answers
54 views

Is it possible to have a partition to distribute transaction load horizontally in SQL Server 2016?

In Cassandra, you can have a partition key to align partitions to particular server nodes. My understanding is that you can distribute transactional responsibility horizontally in SQL Server 2016 (...
1
vote
1answer
890 views

Security risk of Enabling MS DTC Distributed transaction?

I'm doing data exchange and Insert update operations over the linked server between two database servers. To allow distributed transaction, I've changed settings in Windows to enable MS DTC services. ...
1
vote
1answer
472 views

SQL Server Rollback nested Stored procedures and linked servers

I have read THIS StackOverflow article, and found it enlightening. However, I am having trouble assimilating that knowledge with my limited knowledge of transactions and linked servers. I am trying to ...
16
votes
4answers
5k views

Blockchain (Bitcoin) as a database?

I was reading this BBC News article and the following excerpt, caught my attention. It sounds like Always On Availability Groups or High Availability Mirroring, maybe with security automatically ...
4
votes
2answers
645 views

Do SQLCLR objects support Distributed Transactions on regular / external connections?

The most common usage of regular / external database connections in SQLCLR objects, it would seem, is to do SELECT-only queries (i.e. no changes: DML, DDL, DCL, D*%$#!L, etc). It is also fairly common ...
4
votes
2answers
1k views

Why are transactions running as distributed (DTCXact)?

I am actually trying to analyze and solve some regularly occurring deadlocks that all include at least one transaction named "DTCXact", this makes me ask. I repetitively run the following query ...
12
votes
1answer
6k views

Problem creating a transaction in an SSIS package

I'm working on a package that needs to use a transaction but I'm currently getting the following error: SSIS package "CATS-Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, SSIS....
0
votes
1answer
47 views

What's the best solution for distribution of one database over two servers?

I have a MS SQL Server 2014 database with large scale of data an lots of users; and the RAID level is equal to 10. Now due to the rapid growth of data amount, I'm going to distribute this one database ...
1
vote
0answers
461 views

Can we Link sql windows server with oracle unix server?

while inserting data through linked server it is happening but when i execute the stored procedure in getting error like OLE DB provider "OraOLEDB.Oracle" for linked server "server ip" returned ...
0
votes
1answer
887 views

Orphaned transaction (SPID -2) with UOW ID 00000000-0000-0000-0000-000000000000

SYSTEM: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor) PROBLEM: Today I encountered an "orphaned ...
2
votes
1answer
932 views

Four-part distributed Query issue from 2014 to 2008R2

From a SQL Server 2014 RTM in our DEV environement, I am executing this query using a SQL login. select t.noshipid, y.nofolder from distantserver.distantdatabase.dbo....
4
votes
1answer
3k views

transaction across multiple databases - what is the overhead?

I run the following update, which is one single transaction across 2 databases. Please note I have not committed the transaction. --================================================================ --...
3
votes
2answers
289 views

DTC and AlwaysOn

We're heading into a new infrastructure: HyperV, Violin storage & dualsite setup We're looking into AlwaysOn for dual site, but we are using DTC on many of our systems. Do you know if the ...
1
vote
0answers
2k views

Linked Server vs. Ad Hoc (OpenRowset / OpenDatasource) Distributed Queries

I have an application which needs to grab data from different clients' databases that reside at the client's location. So alongside their normal details such as company name, address etc, I also store ...
0
votes
0answers
1k views

Require distributed transactions for server-to-server communication

I want to know limitations or disadvantages of enabling 'Require distributed transactions for server-to-server communication' on SQL SERVER 2012 please? My SQL database Server 2012 have a subscriber ...
0
votes
0answers
2k views

Multiple Distributors in SQL Server Replication

Per this article, a Publisher can only have one remote Distributor in doing SQL Server Transactional Replication. Each Publisher can be assigned to only a single Distributor instance, but multiple ...
7
votes
2answers
9k views

How to query readonly replica in Always On cluster

We have an Always On "cluster" that consists of 2 servers (there will be more), so one is PRIMARY and other(s) is(are) SECONDARY. The idea was to dedicate SECONDARY as a readonly replica, so it would ...
1
vote
0answers
141 views

What changes to Oracle installation are needed to work with Microsoft MSDTC

We have windows 2008 and 2003 servers that use COM PLUS-MSDTC and distributed transactions with 11g(11.2.0.3) running on SunOS and SPARC hardware. The oracle server was installed years ago by a DBA ...
1
vote
1answer
5k views

routine to backup ssas databases fails with: The XML for Analysis request timed out before it was completed

I have developed my own stored procedure, that through a LINKED SERVER, backups the SSAS databases according to a table value parameter. the code for the stored procedure is below. the problem: it ...
1
vote
2answers
12k views

ORA-02049 and ORA-02063 Errors

Select For UPDATE NOWAIT is performed on a table by 20 processes in parallel The error below is returned by the 4 processes out of 20 (16 processes are successful) SQL_UPDATE_ERROR- : SQL update ...
0
votes
1answer
930 views

UOW's: Relating UOW's listed in MSDTC to SQL Server info

We have some active transactions showing up in MSDTC that have been there for a very long time and don't go away. They're not orphaned or in-doubt, just normal-looking active transactions. We're ...
0
votes
1answer
1k views

Distributed transaction coordinator between SQL Server and Oracle

I'm not sure about distributed transaction coordinator so please bare with me on this, I'm probably asking this question wrong. I know distributed transaction coordinator is used in SQL Server and can ...
4
votes
2answers
80 views

Simultaneous money withdrawls from a distributed/replicated database

Lets say I have $10 in my bank account. The database is replicated in North America and Asia. At the exact same time I do a withdrawl of $10 in N. America and in Asia, meaning that my withdraw would ...
6
votes
1answer
6k views

Security implications disabling promotion of distributed transaction for linked server

I've a a linked server and I need to run the following statement: INSERT INTO...EXEC linkedserver.sp @parameter Both servers SQL Server 2008R2 SP1. Once I run it I get this error: Msg 7391, Level ...
2
votes
1answer
1k views

Automatically rollback two-phase transactions on session disconnect

I'm using two-phase commit in Postgres to reliably work with an external system (Amazon EC2). I.e. I first do a PREPARE call, then call the external system. If the external system call fails for some ...
1
vote
1answer
1k views

Oracle temporary table usage with XA

I'm confused about the compatibility of Oracle XA with global temporary tables. I've read several places on the internet and in the oracle documentation that say "Distributed transactions are not ...
5
votes
2answers
56k views

How can I enable distributed transactions for a linked server?

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 ...
6
votes
1answer
382 views

Is there a way to implement a cross-database task on SQL Server 2012 with the Availability Groups feature?

We use SQL Server 2012 and its new Availability Groups (AG) feature. There is a task for moving old data of some tables from one database to another database. Both databases are included into ...
5
votes
1answer
774 views

Distributed transaction and Read Committed Snapshot

Distributed transactions are not supported for snapshot isolation level in SQL Server 2008 R2. But what about read committed snapshot?
7
votes
1answer
8k views

How to insert in table from remote stored procedure without creating a distributed transaction?

How can I insert in a local table from a remote stored procedure without creating a distributed transaction? I want to run a stored procedure on a remote server and use the output as the source for an ...
3
votes
1answer
1k views

Does openquery use distributed transactions?

Does openquery in SQL Server 2005 use distributed transactions? If so, in which cases does it?
2
votes
2answers
90 views

On which server does a query execute?

Given two Microsoft SQL servers: Say ServerA has a view titled "View1": SELECT * FROM ServerB.database.dbo.Table1 WHERE Column1 IN (1,2,3) and a stored procedure "sp_Foo": SELECT * FROM View1 WHERE ...
3
votes
1answer
6k views

Msg 7391, Distributed Transactions (DTC) on SQL Server

This may be DBA related or developer related, I honestly don't know. I have two Database servers: A: SQL Server 2005 (Trigger that uses DBMail on Server B) B: SQL Server 2008 R2 (DBMail configured) ...
4
votes
1answer
1k views

Two-phase commit for distributed transactions: what if a commit failed?

I have 2 servers participating in 2PC scenario: * Srv1: votes OK * Srv2: votes OK * Coordinator runs "commit" * Srv1: commits * Srv2: does not accept "commit" command because of network failure From ...