0

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.

ITHelpGuy
  • 109
  • 1
  • Hi @AaronBertrand, To illustrate the example I have one `SELECT` but I have a few `SELECT` `UPDATE` & `DELETE` clubbed into one transaction. – ITHelpGuy Oct 07 '21 at 02:46
  • Could this issue arise due to database fragmentation? There are several other stored procedures which access this remote database but only one stored procedure is running into this issue. – ITHelpGuy Oct 07 '21 at 03:29

0 Answers0