Linked server error

• osmancebeci

  Newbie

  Points: 1

  More actions

  October 30, 2007 at 5:59 am

  #70380

  Hello,

  SQL Server version is Enterprise Edition 8.00.2039 SP4

  and one oracle database linked via Microsoft OLe DB provider for Oracle. Oracle db name: SCP.

  One of the job inserts data to sql database from querying oracle database; randomly fails with the error message below.

  **************************

  INSERT INTO mom_services ('SQL TABLE')

  SELECT*

  FROM OPENQUERY(SCP,'SELECT SERIAL_NO_ FROM SCADMIN.DEVICEM1 WHERE CONTAINER=''MOM''')

  ***************************

  Server: Msg 7399, Level 16, State 1, Line 3

  OLE DB provider 'MSDAORA' reported an error.

  [OLE/DB provider returned message: Oracle error occurred, but error message could not be retrieved from Oracle.]

  OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

  What I have done;

  Oracle client uninstalled server restarted and oracle client installed .

  Linked server deleted and recreated but nothing changed.

  Any ideas ?

  Osman

• Key DBA

  SSCertifiable

  Points: 6029

  More actions

  November 12, 2007 at 2:13 pm

  #750375

  Osman,

  Can you please give us a little more information to work with?

  What version of Oracle is the target database?

  What version of the Oracle client are you installing on the SQL Server machine?

  From the SQL Server box:

  Can you Ping the Oracle server?

  Can you TNSPING the Oracle Server?

  Can you connect to the Oracle database via SQL*PLUS?

  Can you retrieve data from your target table via SQL*PLUS?

  Is your SQL Server 2000 box a 32 bit or 64 bit install?

  What is your SQL Server version?

  Please include the syntax you are using for creating your Linked Server as well.

  Regards,

  "Key"
  MCITP: DBA, MCSE, MCTS: SQL 2005, OCP