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 kommentar(er)
