Once you go to the Provider tab at the top left you should see something like “Oracle Provider for OLE DB” listed. Then rename the entire file including the extension to Test.udl and press OK. On the desktop create a new text file (make sure to show extensions so you can see the. Listed below are the sites on where to download the proper provider needed:įor this example we are using 64 bit Oracle version 11gįor a quick test to verify you have it downloaded and installed properly you can do a quick UDL test. So at this point we would need to download and install the proper ODAC provider from ORACLE to get that process started. Now that we know the Oracle server is setup and we have our tnsnames.ora information ready, we need to start setting up the SQL Server to have the ability to create a Linked Server that connects to an Oracle database. The Service ID you have setup will be the connection information you will need when creating the Linked Server in SSMS. More normal default Location isĬ:\\product\11.2.0\dbhome_1\ NETWORK\ADMIN\tnsnames.oraĮx: C:\OracleDatabase\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora I need to make sure I can locate it on the Oracle database server itself. This file is called the tnsnames.ora file. Now after I got my Oracle server up and operational I needed to find a very distinct file as this is the file that deals with the connectivity between Oracle and SQL. Once I had the Oracle side all up and ready I started to create my Linked Server in SSMS. Then after creating the table I added data to it so I can compare the results between the Oracle database and the Linked Server results. What’s a table without any data? I added some test data so I can compare the results between the Oracle database and the Linked Server results. Then I needed to create a listener which I learned is very important from an Oracle’s standpoint to make the database run properly. You can download the bits using the following link.Īfter all that, I created a table in the system (default) schema. So for me to understand how the Oracle side worked I needed to get an Oracle server up and running.Īs such, I decided to create an Oracle 11G server. By having the whole picture I feel we can really help understand Oracle Linked Server setups better. For example, in one case I did not really have a good understanding of the ODAC Providers (Oracle’s Providers for connecting to different tools and applications) and the tnsnames.ora file and how they related to the whole setup. The one thing that really got me was I did not really understand how the Oracle side of things worked for me to better troubleshoot the issue. In less than a month I got 4 Oracle Linked Server cases that all had different issues. Being in Business Intelligence Support we deal with plenty of connectivity issues and this is one topic of connectivity that does not get touched on a lot. I have had more Linked Server cases that are setup for an Oracle database than any other non-SQL Server database in SQL.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |