July 26, 2017 at 9:32 am
Good Morning!
SQL 2016 using Oracle 12c client to talk to Oracle 12c database via a linked server. I am running the query below and it is somewhat consistently only returning 200 rows.
select COUNT(*) FROM OPENQUERY(ORACLE_sbmP, 'select psh.* from peoplesoft.PS_SU_TERM_ALL psh') as LnkTbl;
Occasionally returns 30k rows (correct amount) More often it returns 200 rows
select * FROM
OPENQUERY(ORACLE_sbmP, 'select psh.* from peoplesoft.PS_SU_TERM_ALL psh') as LnkTbl2;
select * from
openquery(ORACLE_SBMP, 'select count(*) from peoplesoft.ps_su_term_all') as LnkTbl3;
select count(*) from ORACLE_SBMP..PEOPLESOFT.PS_SU_TERM_ALL
Each of the above three have always returned the correct amount of rows. Any idea what would cause the inconsistency with the first statement?
July 26, 2017 at 9:59 pm
On the surface, I don't see anything in the query that would cause inconsistencies. Have you run the query directly on the Oracle database to see if the table is actually varying that much in the number of records.
That said, the best way to write the linked server query is to have the remote side do as much work as possible, and return as few records as possible across the network. In this case, it would be
select * from openquery(ORACLE_SBMP, 'select count(*) from peoplesoft.ps_su_term_all');
July 26, 2017 at 11:13 pm
Looks like there are similar posts on here and other sites around this issue. Try the resolution in this one?
https://www.sqlservercentral.com/Forums/Topic1157335-20-1.aspx
but I am with crow on this one... do the count on the oracle side.
July 26, 2017 at 11:21 pm
crow1969 - Wednesday, July 26, 2017 9:59 PMOn the surface, I don't see anything in the query that would cause inconsistencies. Have you run the query directly on the Oracle database to see if the table is actually varying that much in the number of records.That said, the best way to write the linked server query is to have the remote side do as much work as possible, and return as few records as possible across the network. In this case, it would be
select * from openquery(ORACLE_SBMP, 'select count(*) from peoplesoft.ps_su_term_all');
Yes I have and the result is the same as the bottom two queries. I too would prefer to do the count on the oracle side of the query, but one of my developers came to me when he noticed the issue and I was at a loss as to why it would behave that way. We are using the oracle driver for the linked server!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply