I’m running into an issue where the DTC is not playing nice with me. I have two servers, who we'll refer to as Server A and Server B (different versions, different physical machines, same domain, and same network).
I have two sprocs on Server A which do something identical, namely, get data from an API (the same API) and eventually insert it into a table on Server B. One sproc has all of the functionality wrapped in a try/catch block and all works well. The second sproc does the same with the addition of all the "try" contents being wrapped in a transaction (so that I can roll back if something is amiss). This results in an error - shown below.
OLE DB provider "SQLNCLI11" for linked server "[Server B]" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 105
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "[Server B]" was unable to begin a distributed transaction.
I tested it without the transaction, and it seems to work just fine, though I'd prefer to have the transaction in there. After some research, I came across a setting which I am a bit hesitant to use prior to understanding its effect.
link to the article: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-serveroption-transact-sql?view=sql-server-2017
setting in question: remote proc transaction promotion
I have that set up as the following piece of code, which I'd flip to the "off" position prior to running the cross-server insert, and turn it back on (which is the default) once I'm done.
EXEC sp_serveroption
@server = '[Server B]'
,@optname = 'remote proc transaction promotion'
,@optvalue = 'false';
Is this the correct way to solve the issue?
A KEY reason that Linked Servers Exist is to enable DBAs/Admins to explicitly RESTRICT what kinds of connections and activities are possible between a given SQL Server and any other box/service that it might be able to interact with (given some of the libraries and built-in capabilities that ship with SQL Server to help with 'cross' platform and cross-host communications).
For more background on this, note the INTENTION of what DISABLING ad hoc distributed queries does: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/ad-hoc-distributed-queries-server-configuration-option?view=sql-server-2017
By default this ships so that ad-hoc distributed queries are NOT allowed (preventing someone from effectively running a "SELECT * INTO OPENDATASOURCE('my remote table on a box in my garage', switches, here) FROM dbo.SuperSensitiveInfo" and 'streaming' sensitive data to an AD HOC endpoint).
Or, in other words, Linked Servers aren't 'just' to make connections simpler/easier (by defining them 1x as a commonly-mapped endpoint that's way easier to query that OPEN*() operations) but so that DBAs/Admins can 'signal' that these endpoints have a DEGREE of trust enough associated with them for an Admin to EXPLICITLY create a connection.
All of which is to say: A key reason that Linked Servers exist is to help 'define' secured endpoints that a given SQL Server might talk to - and under exactly which conditions (i.e., what's allowed and what ISN'T allowed). So, in that sense, Linked Servers are bit like handing a child a smart-phone, where ALL they can use is the phone app, and they're NOT allowed to DIAL any numbers - but can ONLY call out the CONTACTS that you've defined - so that they can talk with friends and call you, but not make any 'ad hoc' calls.
With that 'mindset' as a backdrop, remote proc transaction promotion represents an ELEVATED 'permission' that can be enabled (well, DISABLED, or UN-DISABLED) against a Linked Server IF there' sufficient trust there.
Or, in other words, IF you trust the relationship between ServerA and ServerB and you are OKAY with them playing DTC-footsie together, then I would SET (and 'forget') this Linked Server setting INSTEAD of toggling it before/after this specific sproc was enabled.
Otherwise, I BELIEVE you can/could simply bypass SQL Servers additional RESTRICTIONS here (against DTC enlistment as a DEFAULT) by enabling Network DTC at the OS/Server-Level itself as per steps like these: https://support.resolver.com/hc/en-ca/articles/207161116-Configure-Microsoft-Distributed-Transaction-Coordinator-MSDTC-