Creating a linked server Oracle to MsSQL and common Error 7302 Could not create an instance of OLE DB provider ‘MSDAORA’
May 20, 2011
In some cases it can be handy if you can query a database from one database server to the other and copy data and tables.
Now the fun part is, we can use this on different databases as well, like Oracle, MySql e.g. how cool is that!
Let's set up a linked server to Oracle.
If you don't have oracle available, you can download and install the Oracle Express edition, which we can get for free from the Oracle site.
After you have succesfully installed Oracle server, fire up MSSQL.
First we are going to create a new database for this Linked Server Project, let's call it the 'FunFactory' a great program from the '80.
Insert some tables and rows and we're done! Now we open the Server Objects in the Object Explorer and right click 'add new linked server':
Now it's very important to fill in the right credentials: You need to connect Oracle on it's TNS name, for example, Oracle Express the default is XE, now press 'script from', to see the script that will execute when you press ok.
Ok, enought with the boring GUI, we are going to create another linked server, but this time we script it:
DECLARE @vcLinkedServer VARCHAR(255)
DECLARE @vcLocalUser VARCHAR(255)
DECLARE @vcRemoteUser VARCHAR(255)
DECLARE @vcRemotePass VARCHAR(255)
DECLARE @vcRemoteTNS VARCHAR(255)
DECLARE @ncStatement NVARCHAR(MAX)
SET @vcLinkedServer = 'FunFactory'
SET @vcLocalUser = SYSTEM_USER
SET @vcRemoteTNS ='XE'
SET @vcRemoteUser = 'Fun'
SET @vcRemotePass = 'Factory'
SET @ncStatement = 'USE MASTER
GRANT ALTER ANY LINKED SERVER TO ' + SYSTEM_USER + '
USE ' + DB_NAME()
EXEC sp_ExecuteSQL @ncStatement
EXEC sp_DropServer @vcLinkedServer, 'droplogins'
EXEC sp_AddLinkedServer @vcLinkedServer, 'Oracle', 'OraOLEDB.Oracle', @vcRemoteTNS
EXEC sp_AddLinkedSrvLogin @vcLinkedServer, 'FALSE', @vcLocalUser, @vcRemoteUser, @vcRemotePass
DECLARE @vcTableName VARCHAR(255)
DECLARE @vcFieldName VARCHAR(255)
DECLARE @vcFieldType VARCHAR(255)
DECLARE @vcColumnsDest VARCHAR(MAX)
DECLARE @vcColumnsSrc VARCHAR(MAX)
DECLARE @iIsNullable INT
That's better! To check if both linked servers excist, we do a spfile check:
Great, this part seems to work!
Now we need to make sure we can establish a connection to Oracle
make sure Oracle is installed properly or it will result in some common errors:
Error 7302: Could not create an instance of OLE DB provider 'MSDAORA'
There is 2 things you need to do here:
Check if provider oraOLEDB is available
And make sure ‘Allow In process’is checked in the provider options
If it still does not work:
Make sure correct OLEDB (and oracle client) are installed. If not, search for ODAC on the Oracle site.
In our case SQL is on a 64 bit server, so we choose the ODAC x64 zip file
In order to get it installed, we need an ‘oracle way’ just running the install.bat won’t do the trick, or oracle wouldn’t be oracle 😉
We run the install from cmd or Powershell:
D:\ODAC112021_x64>install all d:\oracle orahome
D:\oracle>configure all orahome
On last thing could stand in your way, a classic oraclient failure:
The path is not inserted in the environmental settings:
Go the PATH and add the location of the newly installed oracle home.
Now the Oracle dir is created on your computer and the linked server is airing, Hooray! It kinda feels like swearing in the Church, installing an oracle client on your SQL server, but what needs to be done….
You can now write queries to copy oracle tables over to your SQL server and what ever you want to do with data in a database!!! I absolutely love this Linked server thingy!
In my next Linked Server post, I will show you some cool stuff that you can do with data in Linked servers! note: here is the link: Creating jobs and queries with linked server
After installing a new service pack to the mssql server, the linked server stopped working, giving an 7302 error 'Could not create an instance of OLE DB provider 'MSDAORA'. If your LS stops working after it has been working fine for months and all the tips above are working, i found a quick work around to get your Linked server to work again, just simply check the dynamic parameter box and save and then uncheck it again. Weird but it does work!
If this quick fix does not do the trick, I wrote a more detailed troubleshoot on linked server here: Troubleshoot linked server
8 Replies to “Creating a linked server Oracle to MsSQL and common Error 7302 Could not create an instance of OLE DB provider ‘MSDAORA’”
[…] addition to my former blog post Create a Linked server, I will now show you what you can do with the linked server and how to run scheduled jobs between […]
[…] building a linked server – Creating jobs and queries with linked […]
[…] – building a linked server […]
Thank you for the tip on error 7302. Checked the box and my connection came right up.
Great to hear some feedback! I’m glad your problem is solved with a tip on provider options.
[…] addition to my previous linked server tutorials, I decided it is time to add MySQL to the linked server series. In order to have the bug tracking application, Mantis migrated from linux and have it run on a […]
you saved my day!.. Thank you so much for this post.. my problem was post installation the path was not updated in the environmental settings. Adding path and later restarting SQL Server instance fixed my issue.
Yeeh! I’m glad it was of help. I was struggling with the environmental settings too I remember. It’s a real showstopper.