January 2, 2008 at 3:56 pm
I have a 64 bit sp2 2005 sql server 2 node active/passive cluster. I've installed oracle 10.2.0 64 bit drivers, and the 32 bit drivers. Tried to create a linked server, but when I select the oracle provider, the dialogue box hangs. I'm able to create new connections through existing SSMS sessions, but cannot bring up a new instance of SSMS and connect in the management pane on the left.
Got my development machine working fine, but this production machine refuses to cooperate. Tried uninstalling and reinstalling twice now with no luck.
Thanks for any ideas.
January 11, 2008 at 10:48 am
bump?
February 8, 2008 at 12:50 pm
1. Install the 64bit Oracle 10g Release 2 client on both nodes (default location for Oracle Home)
2. Install the 64bit Oracle ODAC 10g Release 2 drivers on both nodes to the same Oracle Home you just created
3. Restart SQL Service to get the OraOLEDB.Oracle driver to show up under Server Objects\Linked Servers\Providers (I know this is a painful step, but it IS REQUIRED.)
4. Select the OraOLEDB.Oracle driver in SSMS, right click, and click Allow inprocess
5. Create your linked server in the following format:
[font="Times New Roman"]exec sp_addlinkedserver @server='Linked_Server_Name', @srvproduct='Oracle', @provider='OraOLEDB.Oracle', @datasrc='OracleHostName:portNumber/OracleDBName'
exec sp_addlinkedsrvlogin @rmtsrvname='Linked_Server_Name', @useself='FALSE', @rmtuser='login', @rmtpassword='password'[/font]
By using @datasrc = Host:port/DB you bypass having to keep a TNSnames.ora file in sync on both nodes.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply