Oracle73 Linked Server 1

[eb24]

I am a bit astonished as to the lack of documentation on setting linked servers via SQL Server to Oracle sources. I have perused numerous URLs and forums since early yesterday morning, and still can't seem to find a proper solution. I also have 3 MS Press books and one other one on SQL Server and all of these skim the subject of linked servers!

This is what I do know and have done (hopefully it'll help you experts out more in assisting me):

1. On my client PC's EM, I can successfully run DTS jobs that query both of my Oracle sources with no problems.

2. On my PC, with MS Access, I can successfully link to those Oracle sources via ODBC after I installed Oracle Client Tools. This is utilizing the driver Oracle73, version 2.05.03.01, company Oracle Corporation and file SQL32_73.DLL.

3. Once the jobs are scheduled, the jobs fail on the SQL Server because what is listed below (I believe):

4. I recently installed the same Oracle Client Tools and ODBCs onto the SQL Server, but haven't rebooted yet (will do this tonight). Is rebooting necessary for SQL Server to recognize the new Oracle installations? None of my references say to do this, but I read in one place on the web, thus I will try this.

5. On the server's EM, I picked the Linked Servers node and chose New Linked Server with the following settings:

a. Linked Server: DATA1.ORACLE
b. Provider name: Oracle Provider for OLE DB (is this correct??)
c. Product name: Oracle 7.3
d. Data source: I picked the same name that was located in the TNS.ORA file by the SID attribute, e.g. DATA123
e. Provider string: ??? don't know what exactly goes here; I have seen tons of examples, but don't know how to access this data; I have also heard of aliases for this attribute and don't know exactly what to do?

I have tried different combinations of attributes but nothing seems to work. I believe it is the Provider Name I am picking. Will rebooting the system have Oracle73 in the drop-down? How about the Provider String?

Thanks!

 

[ecobb]

I've done this on Oracle 8i, so maybe this will help. Here are my settings in the EM for my Linked Server:

a. Linked Server: Whatever you want to call it
b. Provider name: Microsoft OLE DB Provider for Oracle
c. Product name: (left it blank)
d. Data source: No - At the beginning of the entry in your TNS.ORA file, you should have something like DATA321.YOURDOMAIN.COM, whatever is in front of your domain name is what you should have listed for the DataSource (in this example, it would be DATA321)
e. Provider string: (left it blank)

I always provide a remote password and login under the "Security" tab as well. As I said, this is for Oracle 8i, but I hope it can help. Here's the thread I started when I was going through this, there's some links to some good articles that really helped me out. (If I'm not mistaken, I think you have to do something with the registry keys on the SQL Server as well) thread962-697079



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher