transfer one db from oracle9i to sql server2000??

• Papillon

  Hall of Fame

  Points: 3303

  More actions

  March 28, 2006 at 10:06 am

  #111503

  I'm having trouble querying from my SQL server 2000 db on my local machine to remote Oracle server 9i. both on same machine

  I can get the server link setup with both MS OLE DB and the Oracle OLE DB drivers

  EXEC sp_addlinkedserver

  @server = 'DBTest',

  @srvproduct = 'Microsoft OLE DB Provider for Oracle',

  @provider = 'MSDAORA',

  @datasrc = 'local_machine_name',

  GO

  EXEC sp_addlinkedsrvlogin

  @rmtsrvname = 'DBTest',

  @useself = 'false',

  @rmtuser = 'test',

  @rmtpassword = 'test'

  GO

  EXEC sp_tables_ex DBTest

  GO

  i got this error

  Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20OLE DB provider 'MSDAORA' reported an error.

  [OLE/DB provider returned message: ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

  ]

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

  please help me out

  ---

  Regards,

  Papillon

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  March 28, 2006 at 1:44 pm

  #629291

  Hi,

  there is a file TNS Names in Oracle client that has to have entries. My Oracle user did it on my computer for her database.

  I would ask an Oracle DBA to check all settings.

  Regards,Yelena Varsha