Updated on: 20th November 2020

I see many visits to this thread from Microsoft Technet references & one of the visitors left me the following comment:

For 12c client, I tried applying the steps you described and it did not work.
 I found that there is a bug affecting Oracle Provider for OLE DB – Version 12.1.0.2 and later
 Oracle OLEDB Throws Error “DBCOLUMNFLAGS_ISFIXEDLENGTH” to Handle CHAR(n BYTE) From Linked Server With MS SQL Server (Doc ID 2484393.1)

Thanks Larisa for your kindness to share the information & found that the error is tightly related to Oracle database version 12.1.x.x. Oracle note provides a workaround using OPENQUERY. I don’t think a “workaround” as a permanent solution, Instead, if possible, upgrade your database to 12.2 or higher versions as a possible permanent fix. Oracle claims this bug was addressed by “Windows Database Bundle Patch 26720785 and later versions”. If you don’t have Oracle’s valid support contract, will not able to download the patch & the only possible solution could be upgrading your 12c database while the download repositories still holding the 12c media. So hurry up :)

As Organizations grow, will end up with multiple software solutions taking care of different areas of the business. Based on the availability, there would be situations where business will end up with different database technologies and data exchanges between such will become a mandatory element to avoid duplication and additional efforts.

Today we will discuss about a requirement that involves MS SQL Server and Oracle database. While Microsoft has well established solutions called as Linked Servers, that allows the MS SQL to connect with heterogeneous database technologies, Oracle’s approach is pretty tedious and may require more efforts that one could anticipate. Yes, I am talking about Oracle “Golden Gate”, which allows an Oracle database to access other database technologies through a “gateway”

Instead of discussing the complex “Golden Gate”, we will see how simple (& complex at the same time) it is to setup Linked Servers from Microsoft SQL Server (for Oracle)

Install the client

Depending upon your OS architecture(32Bit/64Bit), you need to install the appropriate Oracle client. I suggest, install 64Bit Oracle Client on 64Bit OS and 32Bit client over 32Bit OS. You save loads of efforts by doing so. I always do a full installation of the Oracle client and later add missing components those are mandatory for “Distributed Transactions”. So what is a distributed transaction?

• Your MS SQL Database table receives a row (record)
• Your expectation is to replicate the same row to Oracle table
• You have an “after insert” trigger defined with the MS SQL Table

A full installation of Oracle client (Administration) doesn’t install the mandatory component to facilitate the above requirement. You must install “Oracle Services for Microsoft Transaction Server” in order to do such a distributed transaction from MS SQL to Oracle database, after invoking the Client Installer. Make sure while the installer kicks in, you select the already existing Oracle home to make sure that the installation will not create another home for the additional components those you will install. Cross check whether the Oracle OLE DB driver is installed & install the Oracle Services for Microsoft Transaction Server component.

Analyze your Oracle provider and make changes to the stack

Make sure your provider, in our case, “OraOLEDB.Oracle” is configured prior you create linked servers.

Enable the following options

• Allow inprocess
• Nested Queries
• Support “Like” operator

and disable

• Index as access path. Disabling this option is mandatory for “Distributed Transaction”. If your Oracle table has indexes and this option is not disabled, an insert attempt from table trigger will fail with the following error

The OLE DB provider “OraOLEDB.Oracle” for linked server “ERPPROD” returned a “NON-CLUSTERED and NOT INTEGRATED” index “XXFPPUNCHM_N1” with the incorrect bookmark ordinal 0.

Create a linked Server

Right click on the “Linked Server” node and select “New Linked Server…”. I am providing you details of a linked server that is already created at my end. Adjust your linked server details accordingly. Make sure, the Oracle client installation folder is your “PATH” & that the tnsnames.ora file has an entry for the Oracle service (that you will enter in “Provider string” column.

If you have entered the mandatory elements correct, you have successfully created a linked server. If anything goes wrong you will be prompted about it, and address them. Please note, you can always revisit and change the Server options at later stages. However, options under “General” cannot be modified. If requires modifications, you need to drop and recreate the linked server once again.

Test the newly created linked Server

The actual issues starts from now. Please note, my laptop that I use for all developments has multiple versions of Oracle database and clients installed in addition to .Net development tools, Android, PHP etc to name few. If you are planning to implement the linked server solution for a production environment, make sure you have only one Oracle product installed along with MS SQL server. On the other hand you are going to have end number of complexities, few of which are not easily addressed.

For example, my development machine has:

• Oracle database 11g
• Oracle Client 12c (12.1)
• Oracle Database 12c (12.2)

Which is a more than complex situation to address when it is all about MS SQL linked servers. One of the toughest issues to address is the following error

The OLE DB provider “OraOLEDB.Oracle” for linked server “” supplied inconsistent metadata for a column. The column “” (compile-time ordinal 2) of object “””.””” was reported to have a “DBCOLUMNFLAGS_ISFIXEDLENGTH” of 16 at compile time and 0 at run time.

It took me almost 1.5 days to figure out what could be wrong as a simple SQL query like following against the newly created linked server continuously provided me the above error.

Select count(*) from [ERPTEST]..[APPS].[XXFPPUNCHM] 
 go

I’ve come across a post over stackoverflow.com which said, this could be due multiple Oracle products being installed in the same box & there were few instructions to overcome, which didn’t workout for me. However, I was successful with the production server, in which I only had the Oracle 11g client installed. To insure the real time replication of the data from MS SQL to Oracle database, I had to alter few registry values & restart the server. To my utter surprises, the same scenario I tested over three different boxes & all three experiences were different from each other.

• The production server where I have SQL Server 2014 standard edition would not post rows to Oracle database, that is a part of “Distributed Transaction” without the registry hacks.
• My development laptop wouldn’t even fetch rows from Oracle database without tweaking the PATH environment element & registry with proper .dll paths
• My home PC does everything without having to tweak the path of registry where I have almost the same setup like my development laptop. The ONLY one difference with my home PC is, instead of Oracle client 12c, I have 11g client.

Now we will address each of such situations. Please note, the following exercises require you to make registry changes, so please make sure that you take a full backup of the registry prior attempting any given possible solutions. (If you are having ONLY one Oracle product installed (Database or Client, please move to Step#2 )

Step#1: Register Oracle OLEDB driver (This is to insure that we are using the same stack across the solution). Only one version of OLEDB driver could be activated at a time, regardless how many Oracle products are installed. If you had 12c installed after 11g, you must be having the 12c OLEDB driver activated.

From an elevated command prompt, switch to Oracle Client/Database BIN folder (eg: D:\oracle\product\11.2.0\dbhome_1\BIN)

Issue the following command

regsvr32 OraOLEDB11.dll

This should register the OLE DB driver for you.

Step#2: Check your OS PATH environment element, your client/database bin path must be the first Oracle product entry, eg:

This will insure that tnsnames.ora will be sought in this path, in addition to Oracle dlls. As we are using Oracle database 11g as the first product from the list of other Oracle products installed in the environment element PATH (refer the image above), we will hack the registry with all elements related to the specific product (Again, please make a backup of the registry, minimum the specific key)

Registry key: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

Refer the image above and adjust the entries as per your Oracle installation. Once the registry is modified, restart your computer (mandatory)

Once the box restarted, try to insert a row into the Oracle table. Example

insert into [PRODBAK]..[APPS].[XXFPPUNCHM]
 (PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE)
 VALUES(GETDATE(),'IT Office','0046','0')
 go

SQL Management studio must stop complaining about “The OLE DB provider “OraOLEDB.Oracle” for linked server “” supplied inconsistent metadata for a column. The column “” (compile-time ordinal 2) of object “””.””” was reported to have a “DBCOLUMNFLAGS_ISFIXEDLENGTH” of 16 at compile time and 0 at run time.” right after setting up the correct .dll files and the relevant paths in the registry.

Now create your table trigger, through which you want to push a row to the Oracle table. A simple after insert trigger could be defined like following:

create trigger addRecordsToERPTable2 on [UNIS].[dbo].[tRajesh]
 after insert
 as
 begin
 insert into [PRODBAK]..[APPS].[XXFPPUNCHM]
 (PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE)
 Select PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE
 FROM inserted
 end
 go

You may come across an error while a distributed transaction is initiated, Management Studio complaining about “Msg 8501, Level 16, State 3, Procedure addRecordsToERPTable2, Line 13
MSDTC on server ” is unavailable.” This is a pretty simple error to address. Open the Windows Services & check whether the service “Distributed Transaction Coordinator” has started. My development machine initially had this service startup mode set as “Manual”, I changed it to “Automatic delayed start” and started the service. Adjust according to your situation.

Basically the above few things should address most of the common issues you would face with Oracle Linked Server from MS SQL.

Finally, Oracle clearly states, there are limitations using their driver for Linked Servers from MS SQL. So, expect for unexpected while using such a setup. For me, it was simple transactions. If you are expecting rapid replications based on complex business requirements, please do test your scenarios as much as possible prior adapting the above hacks.

regards,

rajesh