Search code examples
sql-serveroracle11glinked-serversql-server-2014openquery

Inserting results from a very large OPENQUERY without distributed transactions

I am trying to insert rows into a Microsoft SQL Server 2014 table from a query that hits a linked Oracle 11g server. I have read only access rights on the linked server. I have traditionally used OPENQUERY to to do something like the following:

INSERT INTO <TABLE> SELECT * FROM OPENQUERY(LINKED_SERVER, <SQL>)

Over time the SQL queries I run have been getting progressively more complex and recently surpassed the OPENQUERY limit of 8000 characters. The general consensus on the web appears to be to switch to something like the following:

INSERT INTO <TABLE> EXECUTE(<SQL>) AT LINKED_SERVER

However, this seems to require that distributed transactions are enabled on the linked server, which isn't an option this project. Are there any other possible solutions I am missing?

Solution

  • Can you get your second method to work if you disable the "remote proc transaction promotion" linked server option?

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