2

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?

sglantz
  • 2,063
  • 4
  • 20
  • 30

3 Answers3

2

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'
Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • This worked. I am feeling a little sheepish about this question now that the answer was so straightforward. In order to run a query on a linked server without distributed transactions, you need to disable distributed transactions for that linked server. I naively believed SQL Server would be smart enough not to attempt it if it was neither possible nor required. Thanks. – sglantz Apr 17 '15 at 00:10
1

If SQL Server Integration Services is installed/available, you could do this with an SSIS package. SQL Server Import/Export Wizard can automate a lot of the package configuration/setup for you.

Here's a previous question with some useful links on SSIS to Oracle:

Connecting to Oracle Database using Sql Server Integration Services

If you're interested in running it via T-SQL, here's an article on executing SSIS packages from a stored proc:

http://www.databasejournal.com/features/mssql/executing-a-ssis-package-from-stored-procedure-in-sql-server.html

Community
  • 1
  • 1
Jon Tirjan
  • 3,556
  • 2
  • 16
  • 24
1

I've been in a similar situation before, what worked for me was to decompose the large query string while still using the query method below. (I did not have the luxury of SSIS).

FROM OPENQUERY(LINKED_SERVER, < SQL >)
  1. Instead of Inserting directly into your table, move your main result set into a local temporary landing table first (could be a physical or temp table).
  2. Decompose your < SQL > query by moving transformation and business logic code into SQL Server boundary out of the < SQL > query.
  3. If you have joins in your < SQL > query bring these result sets across to SQL Server as well and then join locally to your main result set.
  4. Finally perform your insert locally.
  5. Clear your staging area.

There are various approaches (like wrapping your open queries in Views) but I like flexibility and found that reducing the size of my open queries to the minimum, storing and transforming locally yielded better results.

Hope this helps.

Geewers
  • 206
  • 1
  • 8