Linked Oracle Server configuration pain

For the past day I've been forcing myself to work with the Visual Studio Team System 2008 Database Edition GDR R2 for a small SQL Server 2000 database that needs some attention. I wanted Visual Studio to generate a SQL Server 2005 deployment script that I could execute against a local SQL Server 2008 Express instance in order to test a possible migration. During the initial execution of the script I was notified of an error involving a linked Oracle server. No problem, I thought. I'll just create the linked server and run the script again. As happens so often in software development, though, a seemingly quick and easy task turns into a quagmire.

It's been a while since I set up a linked server and I was a little foggy on the setup steps. I was able to determine by examining the existing SQL Server 2000 server that I needed to use the 'Microsoft OLE DB Provider for Oracle' provider. Still using the existing server, I was able to figure out how to fill in the 'Linked Server Properties' dialog and the Security options. As soon as I clicked the OK button, though, I was presented with an error:

The Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3 (or greater) client software installation.

OK- maybe I needed to set the 'Provider string' value. Using this page as a reference (though I'm not configuring IIS), I was able to determine the correct Provider value ("Provider=MSDASQL;DRIVER={Microsoft ODBC for ORACLE};UID=User;PWD=Password;Server=Your_TNSNames_Alias") under item 9.

I recreated the linked server but it still didn't work. I looked in the Registry, and in fact created an ORACLE_HOME string value at HKLM\Software\Oracle. That didn't help either, so I removed that value. (I did notice that setting this value fixed something in Toad 9.7.2 but that story is out of scope for this post.) I even restarted my workstation a couple of times in an effort to appease the Oracle Client God. Nothing worked. My linked server was still broken.

Finally I examined my PATH environment variable. Now, I've struggled mightily with configuring Oracle clients because my company has versions of Oracle ranging from 8i-11. My PATH was collateral damage, and contained references to two Oracle installations. The first reference was to an 11 client's root, and the second was to a 10 client's bin directory. At some point in the past I think that I read that the probing / search for an Oracle client stops when the first client home installation is found. With this in mind, I altered the 11 client path element to point to the bin directory rather than the 11 client root. Restarted the SQL Server 2008 Express service and my linked server was alive and kicking.

So what have I learned after 4 hours of tinkering with SQL Server and Oracle client interaction? Well, there's got to be an easier way. It seems to me that the Oracle client setup and configuration process is opaque. Example: is ORACLE_HOME needed in the registry, as an environment variable, both places, or neither one? The answer seems to be that it depends on the application. I've always been lucky that my installations have worked on a server, but it seems like a fragile process for a volatile developer workstation.