0

I have a scheduled AM process on SQL Server 2008 that imports data from Oracle using a linked server. I am overwriting the imported data from Oracle using drop table, then select into pattern

Apparently, the presence of the "order by" affects my end result! Take a look.

--This works fine to give me the one row I'm expecting from the newly imported table:
drop table t1;
SELECT * into t1 fROM OPENQUERY(ODBC_CSRPT,'
select 
 EXTERNAL_ORGANIZATION_ID  
 ,ORGANIZATION_DESC
 ,STATE
from sysadm.uv_CS_EXTERNAL_ORGANIZATIONS
order by EXTERNAL_ORGANIZATION_ID asc ');
go

select * from t1 
where external_organization_id = '1000107'
go

But this (below) returns no rows. My orgId of 1000107 is now missing? 

drop table t1;
SELECT * into t1 fROM OPENQUERY(ODBC_CSRPT,'
select 
 EXTERNAL_ORGANIZATION_ID  
 ,ORGANIZATION_DESC
 ,STATE
from sysadm.uv_CS_EXTERNAL_ORGANIZATIONS ');
go

select * from t1 
where external_organization_id = '1000107'
go

As you can see the only thing changed is the presence of the order by clause. Another tidbit is that the linked server query is returning same rowcount (51,225 rows to be exact) whether or not the "order by" exists. Any ideas?

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
  • Well, you're dropping the table each time you run this batch...is it possible that the data types are different with each approach? This would help explain why the second query returns nothing. – David Andres Sep 21 '09 at 16:50
  • Does t1 also have 51,225 rows in it after each approach? – Gratzy Sep 21 '09 at 16:53
  • is it possible that the import completes before the actual data is in the table, and that by ordering the import select, the record you are looking for is in one of the top pages, whereas when importing unordered, the record might be in one of the last pages? – Adriaan Stander Sep 21 '09 at 16:54
  • @Gratzy, the row count matches between approaches. – David Andres Sep 21 '09 at 16:56
  • 1
    @anon I would do a union on the table and the linked query you should get 51225 rows if you get more then there are differences in the data i.e if there is 51227 rows then one row is different between the two. I would then track down the different rows and see if that pointed me in the correct direction – Gratzy Sep 21 '09 at 17:08

4 Answers4

0

May be you need recalculate indexes on Oracle?

Alexey Sviridov
  • 3,360
  • 28
  • 33
0

Is there an option like SET ROWCOUNT in effect? In particular on the linked Oracle database. It's been years since I've done anything with Oracle so I don't remember their particulars.

DaveE
  • 3,579
  • 28
  • 31
0

Seems that I'm running into some sort of cap at 51,225 rows. I do not control the Oracle side, another dept at my org does. One of their people say that exact query is supposed to return 51,324 rows.

So, what's really happening is that I'm 99 rows short of the full result set. My "order by" clause re-prioritizes the results, and happened to give me the one row I was looking for. Unfortunately of course, it's still bumping 99 other results out!

I don't know what is significant about the 51,225 number. Hard for me to imagine that the sql server config of the linked server would have that set somehow (I just went with defaults when setting it up).

Worse case, I'll just split the import into 2 queries, ordered oppositely so I cover all the bases. I really need to get to the bottom of that mysterious 51,225 "cap" though...

Thanks much for all the input!

0

Linked server based on Provider: "Oracle Provider for OLE DB" (instead of based on a local System ODBC connection "Microsoft OLE DB Provider for ODBC Drivers") returned the full result set first try.

For some reason, there must be a lower ceiling on the ODBC connection regarding maximum rows that can be returned. Only setting I found was "fetch buffer size," I tried doubling, still same amount of rows returned.

For this query, I'll just use the other linked server.

Thanks again to all for the input!