3

I have a linked Oracle 11g server in Microsoft SQL Server 2012.

I am trying to retrieve a large linked dataset from Oracle and so to limit it I want to just get the first 100 records.

In SQL Server it's simply 

select top 100 from ....

In Oracle the syntax is 

...where rownum <= 100 order by rownum

As soon as my SQL Server 2012 query has more than two joins in it my 

select top 100

causes the following error

OLE DB provider "OraOLEDB.Oracle" for linked server "BIS" returned message "ORA-01403: no data found
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "BIS".

If I take top 100 out, it works but drags too much data over our network. Plus I also need to add a considerable number of joins to the select statement to the 3 I already have before I'm finished.

How can I join a large number of tables in a select statement but only return the first 100 records using T-SQL syntax on my SQL Server 2012 and retrieve these records from Oracle?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ErickTreetops
  • 3,189
  • 4
  • 27
  • 37
  • 1
    Use openquery to query your oracle tables. If you need to join oracle data to sql server, use openquery to populate temp tables and do your join that way. – Dan Bracuk Jul 15 '14 at 02:06

1 Answers1

1

Depending on the logic you want, you could pull the top 100 rows into a temporary table and then use that. I say "depending" because you may want your final query to have 100 rows, which could require more from the Oracle box.

Rob Farley
  • 15,625
  • 5
  • 44
  • 58