1

I am end user on SQL Server 2016 (SP2-CU7-GDR).

I am trying to execute a query of the following nature:

DECLARE @sqlcode VARCHAR(MAX)
SET @sqlcode = 'proprietary sql code'
TRUNCATE TABLE [wundermahndb].[dbo].[Training]
INSERT INTO [wundermahndb].[dbo].[Training]
exec ( @sqlcode ) at OLAP

What I end up with is an error for:

OLE DB provider "OraOLEDB.Oracle" for linked server "OLAP" returned message "Unable to enlist in the transaction.".
Msg 7391, Level 16, State 2, Line 243
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "OLAP" was unable to begin a distributed transaction.

I then tried adding these two lines at the top of my query:

set XACT_ABORT on
EXEC master.dbo.sp_serveroption @server=N'OLAP', @optname=N'rpc out', @optvalue=N'true'

And got the following error:

Msg 15247, Level 16, State 1, Procedure sp_serveroption, Line 27 [Batch Start Line 0]
User does not have permission to perform this action.
OLE DB provider "OraOLEDB.Oracle" for linked server "OLAP" returned message "Unable to enlist in the transaction.".
Msg 7391, Level 16, State 2, Line 243
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "OLAP" was unable to begin a distributed transaction.

I have referenced this post but that solution is what I have tried above which is clearly not working.

I have also looked:

How can I run this query?

(NOTE - I am using SSMS 2017)

UPDATE

If I remove TRUNCATE TABLE... and INSERT INTO..., and simply issue:

DECLARE @sqlcode VARCHAR(MAX)
SET @sqlcode = 'proprietary sql code'
exec ( @sqlcode ) at OLAP

It runs normally and returns the rows.

artemis
  • 6,857
  • 11
  • 46
  • 99

2 Answers2

1

Turn off remote proc transaction promotion option for the linked server:

EXEC master.dbo.sp_serveroption @server=N'OLAP', 
                                @optname=N'remote proc transaction promotion', 
                                @optvalue=N'false'

So SQL Server doesn't attempt to start a distributed transaction with the remote server.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • `Msg 15247, Level 16, State 1, Procedure sp_serveroption, Line 27 [Batch Start Line 0] User does not have permission to perform this action. OLE DB provider "OraOLEDB.Oracle" for linked server "ORAD" returned message "Unable to enlist in the transaction.". Msg 7391, Level 16, State 2, Line 244 The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORAD" was unable to begin a distributed transaction.` Assuming I don't have the proper permissions to do so? – artemis Dec 09 '19 at 21:00
0

From SQL2019 to SQL2017 if you have create linked server for insert operation, then this error comes.

Error: "Msg 7391, Level 16, State 2, Procedure The operation could not be performed because OLE DB provider "MSOLEDBSQL" for linked server "" was unable to begin a distributed transaction."

Then you need to enable MSDTC, to solve this issue.

Open com component settings.

Enable MSDTC where sql 2019 installed. Refer this link for enable help: Unable to begin a distributed transaction

This should start working fine after distributed transaction enabled in com components.

It solved my problem.

MarmiK
  • 5,639
  • 6
  • 40
  • 49