I am using SQL Server 2016 and recently one of the stored procedures I have been using is throwing error. I use a select statement with joins on many tables to the remote server using a linked server
BEGIN TRANSACTION SELECT a.col1, b.col1 FROM [LinkedServer].[Database].[Schema].[TableA] AS a JOIN [LinkedServer].[Database].[Schema].[TableB] AS b ON a.col2 = b.col2 COMMIT TRANSACTION
New request is not allowed to start because it should come with valid transaction descriptor
When I change the
BEGIN TRANSACTION to
BEGIN DISTRIBUTED TRANSACTION I am running into a different error
OLE DB provider "SQLNCLI11" for linked server was unable to begin a distributed transaction.
The remote server too is SQL Server 2016.
Not sure on how to resolve the errors.