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.

2
  • Hi @AaronBertrand, To illustrate the example I have one SELECT but I have a few SELECT UPDATE & DELETE clubbed into one transaction.
    – ITHelpGuy
    Oct 7 at 2: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 7 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.