I need to connect SQL Server 2000 to an Oracle database on another network for which we have permissions. The Oracle tech (they have no dba...a vendor set all of this up) created a schema for us and now I need to access it. They have provided a username and a password.
uname = test
pwd = testpwd
oracle box = ias4_192.x.x.x
In SQL Server how do I make this happen? Do I need to set up an ODBC connection, sp_addlinkedserver, etc. etc. I posted to another forum and was told something about a tnsnames.ora file and possibly a listener.ora file?!? Both the Oracle guy and myself are complete idiots when it comes to this kinda stuff. We each vaguely understand our own system but this heterogenous stuff is killing us! Please help. We need a step-by-step outline to make this happen. Any help would be GREATLY appreciated. Thanks!
In order to connect the SQL Server via sp_addlinkedserver, you need to establish connectivity to the Oracle server.
You need to install the Oracle drivers onto the SQL Server (do a minimum install, just the drivers and the network connectivity tools).
Then you need to open your Oracle Network assistant and configure the connection settings (hostname or ip address and port number). You will need to give these settings a name (called a Net Service Name, I think).
Once these steps are complete, then you can use sp_addlinkedserver to specify the correct settings for SQL Server to "see" the Oracle server. You can also add it through the graphical utility in SQL's Enterprise Manager (under Security/Linked Servers). This latter method may be a bit easier for novices.
Please note, these are the steps that I have used for both Oracle 7.x and Oracle 8.x. They may be slightly different for 9i.