October 2, 2013 at 8:28 am
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply