Sample Header Ad - 728x90

SQL Server linked server query issue

0 votes
0 answers
278 views
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.
Asked by ITHelpGuy (109 rep)
Oct 6, 2021, 11:26 PM