Linked server querying error

• rayan98

  Mr or Mrs. 500

  Points: 577

  More actions

  January 17, 2013 at 4:03 pm

  #287818

  Hi guys, recently i need to start to look after some of the linked server query . i got a instance of SQL 2005 and linked over to oracle server. i can run the pl-sql on the server without a problem (the query returns 200000 rows) on sql plus* in my local machines (the result come back within 25 sec) , when i ran the same query through linked server, it has no responses for about 25 minutes and sometimes it came back the error messages .

  here are the followings :

  OLE DB provider "OraOLEDB.Oracle" for linked server "REB_REPSER" "ORA-03113: end-of-file on communication channel

  OLE DB provider "OraOLEDB.Oracle" for linked server "REB_REPSER" returned message "ORA-12592: TNS:bad packet".

  but if i run a query which return less than 3000 rows, it brings the result back without a problem .

  i have look into different options on linked server setting (and i tried to modify the timeout) without any luck.

  anyone came across this error before ? and how do you solve it ? or where should i start ?

  please let me know if you require any more information ?

  i really appreciated with all your help

  many thanks

• DNA_DBA

  SSCrazy Eights

  Points: 9143

  More actions

  June 26, 2013 at 11:21 pm

  #1627800

  Can you show us the query you are running - is it using openquery?

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  July 16, 2013 at 12:12 pm

  #1632968

  rayan98 (1/17/2013)

  ---

  ORA-03113: end-of-file on communication channel

  Oracle error ORA-03113 means that either the query has timed out or a network glitch has happened - since query is taking 25 minutes (or that is what I understood) I think it is timing out.

  You can manage timeout on sqlnet.ora file - if my memory serves me well it would be SQLNET.EXPIRE_TIME setting the one you are looking for.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• rayan98

  Mr or Mrs. 500

  Points: 577

  More actions

  July 16, 2013 at 1:10 pm

  #1632980

  thanks guys . will try that and let u guys know how did it goes