Microsoft Ole Db Provider For Oracle 11g

/ Comments off
  1. Microsoft Ole Db Provider For Sql Server
  2. Microsoft Ole Db Provider For Oracle Error

I have had more Linked Server cases that are setup for an Oracle database than any other non-SQL Server database in SQL. 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. In less than a month I got 4 Oracle Linked Server cases that all had different issues.

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. 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. By having the whole picture I feel we can really help understand Oracle Linked Server setups better. Walkthrough: So for me to understand how the Oracle side worked I needed to get an Oracle server up and running.

As such, I decided to create an Oracle 11G server. You can download the bits using the following link.

Oracle Database Software After all that, I created a table in the system (default) schema. Then I needed to create a listener which I learned is very important from an Oracle’s standpoint to make the database run properly. 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 after creating the table I added data to it so I can compare the results between the Oracle database and the Linked Server results. Once I had the Oracle side all up and ready I started to create my Linked Server in SSMS. 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.

This file is called the tnsnames.ora file. I need to make sure I can locate it on the Oracle database server itself. More normal default Location is C: product 11.2.0 dbhome1 NETWORK ADMIN tnsnames.ora ex: C: OracleDatabase product 11.2.0 dbhome1 NETWORK ADMIN tnsnames.ora The Service ID you have setup will be the connection information you will need when creating the Linked Server in SSMS. In this case I am going to use SPORTS. 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.

Microsoft Ole Db Provider For Oracle 11g

1 Introduction to Oracle Provider for OLE DB. This chapter introduces Oracle Provider for OLE DB. Oracle Services for Microsoft Transaction Server release 11.1. Oracle Provider for OLE DB OLE DB is a Microsoft data access standard accessed through ActiveX Data Objects (ADO), Visual C++, and any client that can use OLE DB.

Ole

So at this point we would need to download and install the proper ODAC provider from ORACLE to get that process started. REMEMBER – BITNESS MATTERS! Listed below are the sites on where to download the proper provider needed: For 64-bit providers For 32-bit providers For this example we are using 64 bit Oracle version 11g For a quick test to verify you have it downloaded and installed properly you can do a quick UDL test.

On the desktop create a new text file (make sure to show extensions so you can see the.txt part of the name). Then rename the entire file including the extension to Test.udl and press OK. Once you go to the Provider tab at the top left you should see something like “Oracle Provider for OLE DB” listed. Now once you have confirmed you have installed the provider, search for the tnsnames.ora file on the SQL Server.

Normally the default location is – C: app oracle product 11.2.0 client network ADMIN. Example location we are going to use will be: D: app sql2012 product 11.2.0 client1 network ADMIN. What we would add to the SQL Server TNSNames file: SPORTS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11GMG.markg.local)(PORT = 4977)) (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11GMG.markg.local)(PORT = 1521)) (CONNECTDATA = (SERVER = DEDICATED) (SERVICENAME = Sports) ) ) Once you have the tnsnames.ora file correctly filled in on the SQL Server machine, you can now setup the Linked Server in SQL Server Management Studio. Using SQL Server Management Studio to create a linked server to another instance of SQL Server Using SQL Server Management Studio Before we start going through the actual steps you need to make sure the “OraOLEDB.Oracle” Provider is listed under Linked Server Providers. Also make sure that under properties for the provider you select Allow inprocess. When you use the “Allow in-process” option for Linked Server providers, SQL loads the COM DLL in its own memory process.

We do not normally recommend it because it can lead to stability issues in SQL Server, but some providers require it such as the Oracle one. If it crashes, it will also crash SQL Server. When running “Out of Process”, SQL launches the MSDAINITIALIZE process and that process loads the COM server (in this case, OLE DB Provider). If it is idle for x minutes or if the driver crashes the process, it unloads and the next linked server request loads in a new MSDAINITIALIZE process.

Sadly, they didn't have the quarter-pumping classic Burger Time, which would've been pretty fitting. Phoenix arcade game. There's no shortage of entertaining distractions at this amusement emporium and family fun center, which boasts everything from batting cages and driving ranges to volleyball courts and bungee jumps. Although, oddly enough, if you're skilled enough to rescue the president at the end of Bad Dudes, the pixeled version of the Commander-in-Chief states 'Let's go get a burger,' which creates something of a meta experience.

You can see MSDAINITIALIZE by running dcomcnfg and working your way down Component Services. Generally only Administrators or the local system account can launch this, so if SQL is running under a domain account, you should add it to the local Administrators group or have it run as Local System. Now we can start creating the Oracle Linked Server in Management Studio. TITLE: Microsoft SQL Server Management Studio —————————— The linked server has been created but failed a connection test. Do you want to keep the linked server?

—————————— ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) —————————— Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “GDS-IPD”.

Microsoft Ole Db Provider For Sql Server

OLE DB provider “OraOLEDB.Oracle” for linked server “GDS-IPD” returned message “ORA-12154: TNS:could not resolve the connect identifier specified”. (Microsoft SQL Server, Error: 7303) For help, click: —————————— BUTTONS: &Yes &No ——————————. This assumes that somehow we magically have a tnsnames.ora file. From Oracle Database 12c Release 1 (12.1.0.2.0) Oracle Database Client (12.1.0.2.0) for Microsoft Windows (x64) Downloadwinx6412102client.zip (64-bit) (925,039,944 bytes) Select Installation Type (.) Administrator (“Select this option to connect to an Oracle database ”) After installation, I run sqldeveloper.exe, put in Basic connection parameters (the ones our supplier provided).

The connections work and sqldeveloper remembers them (somehow), but it does not store it in any tnsnames.ora file I can find. The only tnsnames.ora I find is the sample one app client product 12.1.0 client1 network admin sample tnsnames.oRA So some steps missing?

Microsoft Ole Db Provider For Oracle Error

. Make sure the Oracle client software on the server running SQL Server is at the level required by the provider. The Microsoft OLE DB Provider for Oracle requires Oracle Client Software Support File version 7.3.3.4.0 or later, and SQL.Net version 2.3.3.0.4. Create an SQL.Net alias name on the server running SQL Server that points to an Oracle database instance. For more information, see the Oracle documentation. Execute spaddlinkedserver to create the linked server, specifying MSDAORA as providername, and the SQL.Net alias name for the Oracle database instance as data source.

The following example assumes that an SQL.Net alias name has been defined as OracleDB. SELECT. FROM OrclDB.MARY.SALES When you reference tables in an Oracle linked server, use these rules:. If the table and column names were created in Oracle without quoted identifiers, use all uppercase names. If the table and column names were created in Oracle with quoted identifiers, use the same case for all letters of the names as was used when the names were created in Oracle. INSERT statements should supply values for all columns in a table even if certain columns in the table can be NULL or have default values.