Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OLE DB provider "MSDAORA.Oracle" has not been registered SQL Server Error: 7403 SQL 2008 on Windows Server 2008 Expand / Collapse
Author
Message
Posted Wednesday, October 2, 2013 8:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:57 PM
Points: 123, Visits: 347
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
Post #1500797
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse