Browsing:

Tag: Linked Server

Oracle ODAC installation x64 – Linked server #7302

This will be my last blog on the linked server to an Oracle database.

Linked server is a great solution for easy datatransfer, but the downsite is, that linked servers and windows updates (security) don’t mix very well. Every time you have security updates  your linked server connection with oracle gets corrupted, to be precise, the linked server provider OraOLEDB.Oracle is.

In my last 3 blogs on linked server, we’ve given you multiple solutions.
Troubleshooting a linked server

– building a linked server

– Creating jobs and queries with linked server

This last blog, will show you the easiest way to solve the issue, to the re-install of the oracle client.

29-4-2013 10-58-24

26-4-2013 16-36-54

In our case, we have 2 installations and 2 homes:
– Oracle Db 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows x64 (Oracle11gR2-Client-x64)
– 64-bit ODAC 11.2 Release 5 (11.2.0.3.20) Xcopy for Windows x64 (ODAC1120320_x64)
De Oracle 11.2.0 Client software:
ORACLE_HOME_NAME: OraClient11g_home1
ORACLE_HOME: D:\oracle\11.2.0\client_1

De Oracle 11.2.0.3.20 ODAC software:
ORACLE_HOME_NAME: OraClient11g_home2
ORACLE_HOME: D:\oracle\11.2.0\client_2

We’ve learned along the way, since this isn’t the first time, and troubleshooting got us nowhere, we do it drastically, we’re gonna reinstall the ODAC server in order to have the corrupt oraOLEDB11.dll reinstalled.
We currently have 2 Oracle trees, Home1 for the client in order to have sqlplus to test connections and home2 is the ODAC installation, used for the linked server. So we start with reinstalling ODAC to see if this fixes the oracle connection.

Reinstall ODAC server 1

Now we open the setup exe from the ODAC1120320_64 and choose to deinstall the ODAC products from home2 and press remove…

29-4-2013 10-34-29
Next thing is to restart our server, since the files are still visible in the folder. and the dll have not unloaded.

29-4-2013 10-34-29

Now, reinstall ODAC

29-4-2013 12-21-24

29-4-2013 12-22-21

29-4-2013 12-24-21

29-4-2013 12-24-49

29-4-2013 12-25-22

29-4-2013 12-26-17

To see what we have installed in both dirs:

AvailableProdComponents home1en2

After this, we restart the server and the linked server works.

If you still experience problems, the ODAC install also comes with a script to update ASP.NET providers:

@@InstallOracleASPNETCommon.sql @@InstallOracleMembership.sql @@InstallOraclePersonalization.sql @@InstallOracleProfile.sql @@InstallOracleRoles.sql @@InstallOracleSiteMap.sql @@InstallOracleWebEvents.sql

— Install OracleSessionState Provider — For Oracle 10gR1(10.1.0.2) database and higher, run InstallOracleSessionState.sql — For Oracle 9iR2(9.2.x) database, run InstallOracleSessionState92.sql instead

@@InstallOracleSessionState.sql –@@InstallOracleSessionState92.sql

I hope these linked server blogs have been easy and back to basic tutorials and you found them useful, please let us know if they’ve been helpful to you.


Troubleshooting a linked server (error 7302 Could not create an instance of OLE DB provider ‘OraOleDB’)

I spent the last two days looking on internet for a solution on a linked server error know as ‘7302’, there are a lot of hits and the same 3 or 4 solutions keep coming up, I tried them all but no success. To save you from going through endless solutions I am going to sum up all the possible solutions, ending with the most valuable solution, well at least it was the best fix for me.

Here are links to linked server articles I wrote before:

building a linked server
Creating jobs and queries with linked server

Now, the dreading 7302 error Could not create an instance of OLE DB provider ‘OraOleDB’
First a solution that saved our asses last time, but this won’t always do the trick! “After installing a new service pack to the mssql server in 2011, 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.”

But this time we didn’t get away with it this easy, almost the same error, different solution needed

once again this error occurs after a series of windows updates we performed, so we started undoing the updates, but ofcourse the error stayed.

On the internet they advise you to change the DCOM security of the MSDAINITIALIZE (Acces Permissions was on Use default, change it to customize, if not already, change the other 2 in customize as well.)

I added the domain user to the permission section. But this solution didn’t work in my case. secondly we opened the Registry and look for: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829} and change the owner to administrator, but in my case administrator was already the owner, so no luck here. (source: http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/09/28/troubleshooting-cannot-create-an-instance-of-ole-db-provider.aspx)

I also checked the Environmental Variables to see if the PATH still contained oracle (e.g. D:/ORACLE/BIN;D:/Oracle/Client;)

Then i tried reconfiguring the Oracle ODAC install in cmd:

D:\oracle>configure
D:\oracle>configure all orahome

but still no luck…

Sometimes restoring your dll files from the day before the error occurs does the trick, but again..nothing.

So, my final solution, re-install the oracle client, this time, with a standard oracle tree that contains some tools to check network and database connection, tnsping and sqlplus.

(re)installing oracle environment:

i used two oracle installations

– Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows x64 (64-bit) this installs the standard oracle home and tools like tnsping and sqlplus

– 64-bit ODAC 11.2 Release 5 (11.2.0.3.20) Xcopy for Windows x64
for a non heavy client for LS to depent on.

first install the oracle 11.2.0.1.0 client and tools.

edit the tns names ora file to your database:

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
Databasename =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = fillinurhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Databasename)

Secondly we reinstalled the ODAC again, now the OraOleDB drivers are available again. Everything is configured for the LS again. MSSQL needs to reload the OraOLEDB file in, for it will keep the old, corrupt file in use, we need to restart the mssql servers.

So if you walked the same path like me, just reinstall oraclient environment. Good luck!

Special thanks to B. Kooiman for helping with the oracle install!


Linked sever mssql to Oracle part 2

In 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 the databaseservers.

In our last post we created a link between a Oracle 10G database and MSSQL2008 server. Now that we established the connection we can schedule jobs in the mssql database to get the data synced with the oracle server.

You can view and query your oracle database in linked server you created under mssql, but you can also send jobs and data to the oracle server

Create a job step and insert a query to specify the data that needs to be copied. We want to insert new and modified organisation data to a financial database.

The code for my Scheduled job:

 DECLARE @dtLastRun DATETIME
 DECLARE @dtNow DATETIME
 SELECT @dtLastRun = CAST(EV000_ALPHANUM_VALUE AS DATETIME) FROM EV000_SYS_PARMS WHERE EV000_APPLICATION='AA' AND EV000_CODE='ZZZ'
 SET @dtNow=getDate()
 BEGIN TRY
 INSERT INTO DECADE_INTERFACE..FinancialDB_RELATIES
 SELECT
 CASE ISNULL(EV870_KEYWORD_2, '') WHEN ''
 THEN EV870_ACCT_CODE
 ELSE EV870_KEYWORD_2 END AS RELA_ID,
 REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, EV870_ENTER_STAMP, 126), '-', ''), ':', ''), 'T', '') AS DATUM_INVOER,
 CASE
 WHEN EV870_AR_DESIG = '0' THEN 'C'
 WHEN EV870_AP_DESIG = '0' THEN 'D'
 ELSE 'B' END AS SOORT_RELA,
 EV870_ALPHA_SEARCH_KEY AS ZOEKNAAM,
 DECADE_LAND_CODE AS TAAL_ID,
 DECADE_LAND_CODE AS LAND_ID,
 0 AS IND_GEBLOKKEERD,
 LEFT(EV870_NAME, 40) AS NAAM_RGL_1,
 '' AS NAAM_RGL_2,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L1, '')
 ELSE ISNULL(EV878_ADDRESS_L1, '') END, 40) AS STRAATNAAM_RGL_1,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L2, '')
 ELSE ISNULL(EV878_ADDRESS_L2, '') END, 40) AS STRAATNAAM_RGL_2,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L3, '')
 ELSE ISNULL(EV878_ADDRESS_L3, '') END, 40) AS STRAATNAAM_RGL_3,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_POSTAL_CODE, '')
 ELSE ISNULL(EV878_POSTAL_CODE, '') END, 15) AS POSTCODE,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_CITY, '')
 ELSE ISNULL(EV878_CITY, '') END, 40) AS PLAATSNAAM_RGL_1,
 '' AS PLAATSNAAM_RGL_2,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L1, '')
 ELSE ISNULL(EV878_ADDRESS_L1, '') END, 15) AS POSTBUSNR,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_POSTAL_CODE, '')
 ELSE ISNULL(EV878_POSTAL_CODE, '') END, 15) AS POSTBUS_POSTCODE,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_CITY, '')
 ELSE ISNULL(EV878_CITY, '') END, 40) AS POSTBUS_PLAATSNAAM_RGL_1,
 '' AS POSTBUS_PLAATSNAAM_RGL_2,
 LEFT(ISNULL((SELECT TOP 1 CC810_COMM_CODE FROM CC810_COMM_DIR
 WHERE CC810_ORG_CODE = EV870_ACCT_CODE
 AND CC810_ACCT_CODE = EV870_ACCT_CODE
 AND CC810_COMM_TYPE = 'MAIN'), ISNULL(EV870_MAIN_PHONE, '')), 15) AS TELEFOONNR,
 LEFT(ISNULL((SELECT TOP 1 CC810_COMM_CODE FROM CC810_COMM_DIR
 WHERE CC810_ORG_CODE = EV870_ACCT_CODE
 AND CC810_ACCT_CODE = EV870_ACCT_CODE
 AND CC810_COMM_TYPE = 'FAX'), ISNULL(EV870_MAIN_FAX, '')), 15) AS FAXNR,
 '' AS CONTACTPERSOON,
 0 AS IND_FOUT
 FROM EV870_ACCT_MASTER
 LEFT OUTER JOIN EV878_ACCT_ADDRESSES
 ON EV878_ORG_CODE = EV870_ORG_CODE
 AND EV878_REF_CODE = EV870_ACCT_CODE
 AND EV878_ADDR_TYPE = 'F'
 LEFT OUTER JOIN U_D_VERTAALTABEL_LANDEN
 ON UNGERBOECK_LAND_CODE = EV870_COUNTRY
 WHERE EV870_CLASS = 'O'
 AND (EV870_AR_DESIG != '0'
 OR EV870_AP_DESIG != '0')
 AND (EV870_ENTER_STAMP >= @dtLastRun
 OR EV870_UPD_STAMP >= @dtLastRun)
 UPDATE EV000_SYS_PARMS
 SET EV000_ALPHANUM_VALUE = CAST(@dtNow AS VARCHAR)
 WHERE EV000_APPLICATION='AA' AND EV000_CODE='ZZZ'
 END TRY
 BEGIN CATCH
 PRINT 'ERROR!'
 END CATCH

We run this job every 30 minutes daily and our job is done, that was easy!

We can write as much jobs and add multiple linked servers as you like.


Creating a linked server Oracle to MsSQL and common Error 7302 Could not create an instance of OLE DB provider ‘MSDAORA’

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
*/
BEGIN TRY
EXEC sp_DropServer @vcLinkedServer, 'droplogins'
END TRY
BEGIN CATCH
END CATCH
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:

exec sp_helpserver

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.

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

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:

C:\Teletraan_I\Mellie>D:
D:\>cd ODAC112021_x64
D:\ODAC112021_x64>install
D:\ODAC112021_x64>install all d:\oracle orahome
D:\ODAC112021_x64>cd..
D:\>cd oracle
D:\oracle>configure
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.

PATH:D:\oracle\bin;D:\oracle;

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

Note:
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