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
SQL
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
Error
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.
