I’m currently setting up a Linked Server from our head office (server A) SQL Server to a subsidiary unit (Server B) SQL Server. The Linked Server connection tests successfully, and I can log into server A from server B and can using query. However, when I try to execute a query using a 4-part name through Linked server, I encounter the following error: > Msg 8522, Level 16, State 3, Line 1 Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction. Environment Details: Head Office (server A) SQL Server: - SQL Server 2008 R2 (already upgraded pack SP3) - Windows Server 2012 - TLS 1.2 enabled - MS DTC service is turned on Subsidiary (server B) SQL Server: - SQL Server 2016 - Windows Server 2016 Standard (64-bit) - TLS 1.2 enabled - MS DTC service is turned on Networking: - The B server connects via VPN to be on the same network as server A - Ping and Telnet tests (IP and port) from both sides work fine - SQL login from server A to server B(via IP and port) is successful USING PROVIDER: i have try to using SQLNCLI11 and MSOLEDBSQL, but both till error DTC. Linked Server test connection: Success Update, insert query: it's ok, but when i try selection query, i got error DTC has stop this transaction. example:

SELECT * FROM [LinkedServerName].[DatabaseName].[dbo].table

Has anyone faced a similar issue? Could this be a MS DTC configuration mismatch or network security/firewall/DTC port range issue? Any guidance on how to properly configure MS DTC across different servers/domains/VPNs would be highly appreciated. Thanks in advance!

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-commit transaction the compiler/SQL Server/something else just prepends all statements with a BEGIN TRANSACTION and appends all statements with a COMMIT TRANSACTION since everything is technically enclosed in a transaction (https://dba.stackexchange.com/questions/43254/is-it-a-bad-practice-to-always-create-a-transaction?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) . I'm sure this is incorrect and the source of my confusion as to why performing a cross-server UPDATE without explicitly stating BEGIN TRANSACTION works but explicitly stating one does not. According to Microsoft (https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-2017) , an explicit BEGIN TRANSACTION on an UPDATE query that references a table on a linked server gets escalated to a distributed transaction; and since distributed transactions aren't configured on the linked server, I receive an error. How does the auto-commit setting avoid this? How does it not get escalated to a distributed transaction? Does the auto-commit setting send the data to the linked server but doesn't "listen" for a response from the linked server via Microsoft Distributed Transaction Coordinator (MS DTC); thus if an error occurs, it "silently" fails? Auto-commit doesn't escalate to a distributed transaction: UPDATE l SET l.RecordKey = s.RecordKey FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s ON l.Value1 = s.Value1; Explicit transaction does escalate to a distributed transaction (and errors in my case): BEGIN TRANSACTION UPDATE l SET l.RecordKey = s.RecordKey FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s ON l.Value1 = s.Value1; COMMIT TRANSACTION; I should add that the statements were run in SQL Server Management Studio.

I've been working on setting up clustered environment with a two new DTC roles, DTC1 and DTC2. I set up DTC1 which allows both clustered VM's and SQL Server Instances to function correctly. However, when I disable DTC1, DTC2 will remain online but no DTC transactions will work. I'm actually going through this issue with a Microsoft tech right now (without much luck). Both DTC services are set up with similar configurations. Anyone here have an idea as to what might be going on? Here's my setup: - One clustered environment - Two DTC Roles - Two Separate ISCSI disks, one for each role - Two VM's (same configuration) - Two SQL Server Instances, on on each VM Networking configuration is allowed, everything looks to be set up properly.

My production environment uses MS DTC and SQL Server 2014. I must have an HA solution such as Mirroring or AG in place. (Not FCI). I am trying to explain to my team that MS DTC is not supported by SQL Server with Mirroring / AG and what could go wrong. But all the examples I am seeing online are for cross database scenarios, which doesn't apply to us. I am looking for a worst case scenario & likelihood of its occurrence. Ideally something that I could actually demonstrate to them. ie: creating a transaction that uses MS DTC, triggering a failover, showing what happens. As a DBA, I would like to be able to explain to the business what are the real world implications of this lack of support for MS DTC from Microsoft, so that they can decide if the risk is worth the cost of: 1. Upgrading SQL Server to 2016 & AGs and/or 2. Changing how their application relies on MS DTC (I am told this is impossible). I don't want to just parrot "Not supported" to them because that's unacceptable. Any help appreciated.

I have a trigger on my SQL Server, located in server7. It inserts a data on another server (server5), after insert, as you see the code below: CREATETRIGGER [dbo].[trgSPTInsereDepartamento] ON [dbo].[tblDepartamento] AFTER INSERT AS BEGIN SET NOCOUNT ON; insert into [Server5].alfabase.dbo.tblDepartamento (CodDepto,DescDepto,IncluiOutroDepto) Select ins.codDepartamento, ins.DescDepartamento, 0 From inserted ins where not exists(select 1 from [Server5].alfabase.dbo.tblDepartamento sptsta where sptsta.CodDepto = ins.CodDepartamento) END When i make an insert, the SQL shows me this: > OLE DB provider "SQLNCLI10" for linked server "server5" > returned message "No transaction is active.". > Msg 7391, Level 16, > State 2, Procedure trgSPTInsereDepartamento, Line 7 The operation > could not be performed because OLE DB provider "SQLNCLI10" for linked > server "server5" was unable to begin a distributed > transaction. Local DTC properties is all checked ( it allows remote users ). I've restarded the server and the DTC service. No Luck for me. I read a lot of posts but none of them helped me. Thanks for the help. > **update1:** Checked firewall. disabled it but still doesn't work. DTC is ok ( only server5 is on a cluster. ) I'm really out of ideas guys. Thank you very much. > **Update 2**: From server7 to server5, I have the problem above. To test, I made a trigger on a database from server4 to server5. and it worked. So, the problem is on server7. Server7 is not on the Cluster (server3,4,5 are clustered).

Long story short I've got my local development machine into a bit of a mess with MSDTC and I have the horrible red arrow in DCOM. I have an application that uses MSDTC and MSMQ, and it's got itself stuck. Just to be clear, the SQL Server instance is Express and it is running on my machine. Everything is local to my machine is this case for my development purposes. I have uninstalled MSDTC, MSMQ and then reinstalled MSDTC and reset the log and then installed MSMQ again, however the issue remains. Clicking on COM+ gives the following error: Delving into MSDTC I can see no active transactions, but curiously the summary totals show 5 transactions, even though none are in the list: Moving over to SQL Server, the following commands all return no rows: - SELECT @@trancount - DBCC OPENTRAN - SELECT XACT_STATE() However, the last query against sys.dm_tran_active_transactions returns 5 rows: It looks like my machine was in the middle of a debugging session, I left it running and then early in the morning, my machine rebooted. I would like to remove these orphaned transactions, but I'm not sure how. Can I just DELETE FROM sys.dm_tran_active_transactions without any negative impact so I can go back to using MSDTC without issues.

as you can see on my screenshot, I have a DTC transaction in error on my server. I know, I can Right -> Resolve -> Forget/Abort However, I want to do it with a command line (batch or Powershell). Is there any way to do that? Thank you

A company is having a Microsoft SQL Server 2017 Enterprise cluster (RTM-GDR, 64-bit, 14.0.2027.2, KB4505224) consisting of one Availability Group (AG) with two servers, a primary and a secondary. A closed source application used by this company seems to work fine with the exception of one piece of functionality. When this functionality is used, the following error can be found in its log file:

Cannot use SAVE TRANSACTION within a distributed transaction

A possible workaround, according to an unrelated website, is to disable distributed transactions by:

ALTER AVAILABILITY GROUP MyaAG
   SET ( DTC_SUPPORT = NONE );

After testing, this seems to solve the issue. However, I am uncertain about the impact of this change. - What impact does this change have? - Is the cluster still active? - Is the data still replicated to the secondary server? - Does it have a positive or negative impact on the performance? - Has a safety feature just been disabled?

**Problem:** How can I run Distributed Transaction Coordinator (DTC) on all servers in an AlwaysOn Availability Group (AG)? I do NOT need to maintain transactions over failover/switchover events. **Setup:** I have a Windows Failover Cluster (WSFC) with three Windows 2008 R2 servers where they are all running SQL 2012. Two servers are in one data center and are part of an AlwaysOn Failover Cluster (FCI), while the third server is in a second data center. The WSFC is a multi-subnet cluster. Here's a sketch of the setup: I've been able to install and configure DTC to work between the two FCI nodes because they are on the same subnet and share storage. I have configured a couple of AGs and they have been working fine. This screenshot shows DTC installed on the FCI: This screenshot shows that I can configure DTC on one of the FCI nodes (whichever is active): I'd like to migrate an application that uses DTC onto this cluster and use an AG. I've read that DTC is not supported with AGs (Reference ). I have not been able to find a way to configure DTC on the third node in the second data center. When I try to configure DTC on the third node, it seems to be unavailable, as shown in this screenshot: In Brent Ozar's Free Setup Checklist PDF for Availability Groups he lists: *Cluster Installation...* *29. If an FCI is involved, configure DTC per your Planning section decisions.* In the comments on SQL Server 2012 AlwaysOn Availability Groups Rock Brent says that "... nothing changes when AGs are in play. Just keep in mind that databases in an Availability Group don’t support transactional consistency when failed over together to another replica..." This makes it seem that DTC can be used in Availability Groups as long as you understand that transactions will not be maintained in a AG switchover. I would not need it to maintain transactions from the FCI nodes. I would just need DTC available for the application to use in the case of a catastrophic disaster (where I lost my primary data center). How do I configure DTC on my third node? Or, is it the case that I'm just out of luck when it comes to using AGs and an application that needs DTC? **UPDATE:** The solution I've settled on is to use Log Shipping. However, in the case of a fail over, I still need DTC to be available on Node3. I've discovered that it becomes available by uninstalling the clustered MSDTC-MSSQLSERVERCLU instance of DTC that is shared between Node1 and Node2. Once removed, I can setup and configure a LocalDTC instance on Node3. Afterwards, I can reinstall the clustered MSDTC-MSSQLSERVERCLU instance. Doing the install sequence in that order seems to work. I've been running like that for a little while now and I have not discovered any ill effects. It seems like this would also work for running an AlwaysOn Availability Group. I understand that distributed transactions would not be preserved in an AG failover, I would just need new ones to work after the failover. But I haven't gone there...

*In a relation to https://dba.stackexchange.com/q/286437/5203 , which is a similar but different question.* We have an SQL Server 2016 for which we are considering creating an Always On availability group for purposes of disaster recovery only. Currently the server is not a part of any cluster or availability group, and: - The server hosts multiple databases - Most of the operations are cross-database transactions - Many of the operations involve actions not supported in distributed transactions (e.g. save transaction) From the fact that currently save transactions etc execute successfully, one can conclude that the server is *not using distributed transactions*. Now, let us consider the following two quotes from Configure distributed transactions for an Always On availability group : - > A transaction with two or more databases in a single instance of the database engine is actually a distributed transaction. The instance manages the distributed transaction internally; to the user, it operates as a local transaction. SQL Server 2017 (14.x) promotes all cross-database transactions to DTC when databases are in an availability group configured with DTC_SUPPORT = PER_DB - even within a single instance of SQL Server. (That *seemingly* suggests that our cross-database transactions are technically distributed because they involve multiple databases. Which contradicts the observation that save transaction currently works, so let us call them "kind-of" distributed, not really "true" distributed.) - > SQL Server does not prevent distributed transactions for databases in an availability group - even when the availability group is not configured for distributed transactions. However when an availability group is not configured for distributed transactions, failover may not succeed in some situations. Specifically the new primary replica SQL Server instance may not be able to get the transaction outcome from DTC. To enable the SQL Server instance to get the outcome of in-doubt transactions from the DTC after failover, configure the availability group for distributed transactions. (This promises woes for distributed transactions executed when no DT protection is enabled in an AG. But does that include both the "kind-of" and the "true" distributed transactions, or only the "true" ones?) **Does the second quote address only "true" distributed transactions, or also the "kind of" distributed transactions that are "distributed" simply because they involve multiple databases, like the first quote explains?** That is, if the only "distributed" transactions that happen are the single-instance ones that just involve several databases from the same server instance, then is it or is it not the case that such "kind-of" distributed transactions are fully protected within an AG as if they were local transactions, even when the AG has DTC_SUPPORT = NONE?

Good morning all I am working on a Biztalk migration project On an alwayson SQL environment Who has an idea if BIztalk is compatible with ALWAYSON if YES it is from Which version? another question for the activation of the MSDTSC function should you activate this option in Always on thank you for your help

I have been told that since MSDTC is not supported for SQL Server 2012 Always On Availability Group and should be turned off. what specific settings do I need to change to turn it off? This is what I currently have:

I've been getting these two errors on SQL Server log: **Error 1:** > DESCRIPTION: Import of Microsoft Distributed Transaction > Coordinator (MS DTC) transaction failed: > 0x8004d00e(XACT_E_NOTRANSACTION). **Error 2:** > DESCRIPTION: Enlist operation failed: > 0x8004d00e(XACT_E_NOTRANSACTION). SQL Server could not register with > Microsoft Distributed Transaction Coordinator (MS DTC) as a resource > manager for this transaction. The transaction may have been stopped by > the client or the resource manager. It happens at random times and I don't know how to track and associate it with any job or application request. No change has been made to the server that I'm awere of. **Here's what I have done so far:** - Verified that Distributed Transaction Coordinator service is running on Windows; - Event Viewer didn't show any error I could relate to the problem; - Firewall is off while I'm troubleshooting; - Followed the instructions to [Configure DTC Security](https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc731495(v=ws.11)?redirectedfrom=MSDN) ; - Have seen similar threads like [this](https://stackoverflow.com/questions/29414250/msdtc-on-server-server-is-unavailable) and [this](https://stackoverflow.com/questions/33883309/the-msdtc-transaction-manager-was-unable-to-push-the-transaction-to-the-destinat) ; - Tried to [uninstall and install](https://learn.microsoft.com/en-us/biztalk/core/troubleshooting-problems-with-msdtc?redirectedfrom=MSDN#consider-reinstalling-the-distributed-transaction-coordinator-service-if-other-troubleshooting-steps-are-not-successful) MSDTC; - Tested the DTC with dtctester.exe and here's the output:
**EDIT 1:** - Ran test using **DTCping** and it showed an error:

To fix DTCping error I [Set the appropriate values for the EnableAuthEpResolution and RestrictRemoteClients options](https://learn.microsoft.com/en-us/biztalk/core/troubleshooting-problems-with-msdtc?redirectedfrom=MSDN#set-the-appropriate-values-for-the-enableauthepresolution-and-restrictremoteclients-options) on the client side; - Tested the DTC with dtctester.exe again and now it worked fine; **EDIT 2:** - Configured **Extended Events**. It captured the error and brought information about the user, database and server involved on the error, but it wasn't enough to find out the culprit. Here's the code used to create the XE session:
CREATE EVENT SESSION [MSDTC_TROUBLESHOOT] ON SERVER ADD EVENT sqlserver.errorlog_written( ACTION(package0.callstack,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.sql_text,sqlserver.username) WHERE ([sqlserver].[client_hostname]=N'MyAppServer' AND [sqlserver].[database_name]=N'MyDatabase')) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO Even though MSDTC passed the tests I'm still getting the two errors on SQL Server log. Is there anything else I can do find out what is causing them?

My application is running in Azure SQL which doesn't support DTC. We only use Azure SQL in upper environments. In the local development environments, we're still using SQL Server 2016. I want to avoid a situation, which has happened a couple of times in past where a developer has ended up writing queries with a transaction that ended up escalating to DTC, which of course was only caught in upper environments. I'm looking for a command to run in SQL Server to completely remove its ability to participate in DTC, basically make it like its Azure SQL counterpart. Edit: I don't want to remove MSDTC from the system itself as I've other services, that doesn't involve Database, that requires MSDTC.

Experiencing this intermittent issue on a few servers running MSDTC. 19/09/2018 10:54:38 a.m. -- A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSDTC$cde1a4e7-dc9d-4ae3-89b9-8114147ae420 service. 19/09/2018 10:54:38 a.m. -- Cluster resource 'MSDTC-SQLServer' of type 'Distributed Transaction Coordinator' in clustered role 'SQL Server' failed. 19/09/2018 10:54:57 a.m. -- The Distributed Transaction Coordinator (cde1a4e7-dc9d-4ae3-89b9-8114147ae420) service terminated unexpectedly. It has done this 12 time(s). All these servers run trend micro deep security agent ( Anti Malware protection and Intrusion Prevention ). The issue completely goes away when AV is turned off. But having the AV turned off for long is not an option. Have configured the following AV exclusions for MSDTC. 1. MSDTC Service 2. MSDTC Log file 3. MSDTC Log Folder are there any more exclusions that I should get added ?

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 against production servers: SELECT DTAT.transaction_id , DTAT.[name] , DTAT.transaction_begin_time , CASE DTAT.transaction_type WHEN 1 THEN 'Read/write' WHEN 2 THEN 'Read-only' WHEN 3 THEN 'System' WHEN 4 THEN 'Distributed' END AS transaction_type , CASE DTAT.transaction_state WHEN 0 THEN 'Not fully initialized' WHEN 1 THEN 'Initialized, not started' WHEN 2 THEN 'Active' WHEN 3 THEN 'Ended' -- only applies to read-only transactions WHEN 4 THEN 'Commit initiated'-- distributed transactions only WHEN 5 THEN 'Prepared, awaiting resolution' WHEN 6 THEN 'Committed' WHEN 7 THEN 'Rolling back' WHEN 8 THEN 'Rolled back' END AS transaction_state , CASE DTAT.dtc_state WHEN 1 THEN 'Active' WHEN 2 THEN 'Prepared' WHEN 3 THEN 'Committed' WHEN 4 THEN 'Aborted' WHEN 5 THEN 'Recovered' END AS dtc_state FROM sys.dm_tran_active_transactions DTAT INNER JOIN sys.dm_tran_session_transactions DTST ON DTAT.transaction_id = DTST.transaction_id WHERE [DTST].[is_user_transaction] = 1 ORDER BY DTAT.transaction_begin_time It always shows results similar to this sample: > 2414848764;DTCXact;2016-01-28 > 10:24:41.983;Distributed;Committed;Committed > 2414896908;DTCXact;2016-01-28 > 10:26:05.847;Distributed;Committed;Committed > 2414903917;DTCXact;2016-01-28 > 10:26:29.017;Distributed;Committed;Committed > 2414918503;user_transaction;2016-01-28 > 10:27:06.823;Read/write;Active;NULL 2414918551;DTCXact;2016-01-28 > 10:27:06.973;Distributed;Committed;Committed This result shows that most transactions run as distributed. I really don't know why at all. Some of the servers are clustered with two nodes. Others are not. Their results are similar. **Facts**: - The MSDTC Service is running on all SQL-Servers. None of the SQL-Servers has any Linked Servers. - remote trans proc in sys.configurations is set to 0. - I found no explicit Begin DISTRIBUTED Transaction or SET REMOTE_PROC_TRANSACTIONS usage in code - no definition / usage of TransactionScope in Code - Found a class described as follows: //COM+ Transaction mgr using "Service without Components" model built according to System.Transactions.TransactionScope (NET 2). I don't know what this means. The asp.net web application causing those transactions usually always reads and writes data from one and the same database on one and the same SQL-Server. There MAYBE some very few exceptions, especially running during nighttime copying some data around, but I would not expect to see the MAJORITY of transactions being distributed the whole day long. I traced the actios of a SPID that I found was running distributed transactions: Here is a sample result of tracing: I see dozends of RPC:Completed Events in a row followed by 2 DTCTransaction Events (1st: EventSubClass="Enlisting in a DTC transaction", 2nd EventSubclass= "Propagate Transaction"). All target the same DB. No TM:Promote Tran xxx Events happen. I found out that those queries are running on the IIS (where the Web Application is hosted) in a .net Windows Service, that uses the same database as well and executes jobs. May this 2nd application cause DTC?? I searched around the web without finding similar questions or any answers. So I wonder what could be the reason for this? Or what knowledge do I miss to understand this?

We've recently installed the newest Microsoft OLE DB Driver for SQL Server (msoledbsql) as it now supports multi-subnet failover capabilities (detailed here: - https://blogs.msdn.microsoft.com/sqlnativeclient/2018/03/30/released-microsoft-ole-db-driver-for-sql-server/) However we are seeing an issue with queries using linked servers. When we run a simple query through linked server "test": - select * from test.master.sys.databases; The error returned is: - Msg 8522, Level 16, State 3, Line 1 Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction. I've checked the settings for MSDTC on both servers (source and target) and they seem to be correct. I've seen online that this can be fixed by explicitly opening a distributed transaction:- BEGIN DISTRIBUTED TRAN select * from test.master.sys.databases; ROLLBACK But that errors out with: - Msg 3910, Level 16, State 2, Line 1 Transaction context in use by another session. Has anyone seen this before? Any help or advice would be greatly appreciated.

I have a linked server setup from SQL Server Ent 2012 to PostgreSQL 9.3 and a stored procedure pulling 3 filtered tables from it and storing each in a temporary table. There is a final query at the end of the Stored Procedure with many left joins, from local SQL Database and it includes these 3 temporary tables. I have disabled "Enable promotion of distributed transactions for RPC". Every 1/6 or more attempts to use this Stored Procedure fails, with error on web server as:` Unknown Error Detected System.Data.SqlClient.SqlException (0x80131904): The operation could not be performed because OLE DB provider "MSDASQL" for linked server "POSTGRESQL" was unable to begin a distributed transaction. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection ` No errors on the SQL Server though. **Linked server definition:** EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRESQL', @srvproduct=N'PostgreSQL', @provider=N'MSDASQL', @datasrc=N'PostgreSQL' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'POSTGRESQL',@useself=N'False',@locallogin=NULL,@rmtuser=N'xxxxxx',@rmtpassword='xxxxx' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'collation compatible', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'data access', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'dist', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'pub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'rpc', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'rpc out', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'sub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'connect timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'collation name', @optvalue=null EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'lazy schema validation', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'query timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'use remote collation', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'remote proc transaction promotion', @optvalue=N'false' **Stored Procedure/Query** (very simplified sorry) ALTER PROCEDURE [dbo].[SP_Name] @DateFrom DATETIME, @DateTo DATETIME AS DECLARE @variableB int SET @variableB= {some criteria} IF (@variableB =''''' +CONVERT(VARCHAR(15),@DateFrom,102)+ '''''' + 't.date =''''' +CONVERT(VARCHAR(15),@DateFrom,102)+ '''''' + 't.date =''''' +CONVERT(VARCHAR(15),@DateFrom,102)+ '''''' + 't.date <=''''' +CONVERT(VARCHAR(15),@DateTo,102) + '''''' {plus other filtering criteria} +'order by a1'')' insert into #temp_table2 EXEC (@TSQL) IF (@variableB < 1000) BEGIN select {many columns} from {various local tables with left joins} left join #temp_table1 on {conditions} left join #temp_table2 on {conditions} left join #temp_table3 on {conditions} END **Local DTC Properties** - What I think is happening, is that the 3 openqueries into temp tables are getting distributed, and somehow that just doesn't work. I confess I don't understand this very well - it's over my head. I also know that enabling "Enable promotion of distributed transactions for RPC" makes the fails happen more frequently. - This again works 5/6 times and fails about 1/6 times. So I am not sure what is happening. - This is a read only transaction on PostgreSQL - I am not sure why distributed transaction is getting invoked at all. **Edit** I see this question here and thinking I shouldn't be having this issue. **Edit 2** I may have a found a duplicate question here .

So I'm doing some research on MSDTC and its role it does or does not play in various types of calls within SQL Server, and it got me thinking on the following questions: 1. Are there any 3rd party alternatives/replacements for MSDTC (if they exist) & their pros/cons 2. Does DTC come into play when I simply query a single table (CRUD or Select) via a linked server? 3. Is MSDTC required for transactional replication? I know updatable subscriptions require MSDTC, but not clear if non-updatable subscriptions need/use MSDTC. From googling, I can't seem to find good answers to these questions, so turning to the community here. Any help in bettering my understanding of MSDTC is greatly appreciated. Thank you!

I am attempting to setup a Linked Server from MS SQL Server 2012 to PostgreSQL 9.3 via Linked Servers & ODBC driver from PostgreSQL. Everything works, until a given query invokes MSDTC, at which point I get an error like this on the SQL Server machine, and the query utterly fails: > The XA Transaction Manager attempted to load the XA resource manager > DLL. The call to LOADLIBRARY for the XA resource manager DLL failed: > DLL=C:\Program Files\psqlODBC\0905\bin\pgxalib.dll, HR=%3, File=%2 > Line=%3.%0 The DLL is in fact that location, so the registry seems to be pointing to the right file. The ODBC driver is 64bit and so is my OS. "File=%2" is pointing to something on the d drive, which doesn't make sense to me, since d drive is a DVD. MSDTC is running... what am I missing? I have toggled Linked Server Properties "Enable Promotion of Distributed Transactions for RPC" to both "True" and "False" and this doesn't change the issue and does not produce a different error. Otherwise, scouring the Internet has brought me nothing. Last thing to point out, my query isn't actually doing any updating - it is just pulling data. So I'm not sure why MSDTC get's invoked in the first place...