OLE DB provider "MSDAORA.Oracle" has not been registered SQL Server Error: 7403 SQL 2008 on Windows Server 2008

• Mile Higher Than Sea Level

  SSCarpal Tunnel

  Points: 4828

  More actions

  October 2, 2013 at 8:28 am

  #150513

  Can someone give me some pointers for a Oracle Linked Server?

  On my Windows 7 ODBC, I can successfully connect MS Access 2010 to Oracle tables.

  Using that - my intent is to link tables to SQL Server that has Linked Tables. This has been very frustrating.

  While I use SQL Server, this is challenging my experience. It is OK to give it to me in beginner terms.

  Installed the Oracle 64 bit ODBC (ODAC) packages. Set the ODBC application to get through firewall.

  ODBC connects to the server (reports successful). There is one difference. The box with the list of Oracle servers is blank. On my Windows 7 (32 bit) the ODBC connects, but the server box shows the list of oracle servers.

  In the SSMS the Linked Servers - Providers shows OraOLEDB.Oracle

  in SSMS sp_helpserver

  the Nav3 network name is NULL, status data access, use remote collation, ID 8, collation_name NULL, connect_timeout 0, query_timeout 0

  in SSMS - expand the catalogs for Nav3, cataloges - default tables - "oraOLEDB.Oracle" for linked server "Nav3", Cannot inialize the source object of OLE DB provider Error 7303

  This script runs with out errors:

  USE [master]

  GO

  EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1

  GO

  EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'DynamicParameters', 1

  GO

  EXEC master.dbo.sp_addlinkedserver @server = N'Nav3', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'Poxxx.xxx.com,1521'

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Oracle HOSTNameHere',@useself=N'False',@locallogin=NULL,@rmtuser=N'theUN',@rmtpassword='thePW'

  -- http://sqlservercorner.blogspot.com/2009/03/linked-servers-for-64-bit-sql-2005-sp3.html