Oracle DBA to learn MS SQL: driver

Showing posts with label driver. Show all posts

Friday, March 9, 2012

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

God morning!
I 've just installed SQL Server 2005 on Windows 2003 Server 64 bit. One of my databases need to connect to seveal Oracle databases.
- I tried installing 64 bit Orcale 9 on this server. Got a strange message that the application (Oracle 64 bit) could not be installed on a 32 bit server! Strange!
- I installed Oracle 9 32 bit on the server, but I could not find ODBC driver in the Data sources! Neither the oledb under providers in MSS Manager Studio!
- Same result efter several desintall and installation. A college copied the .dll files och run a script to register the dll files. We could then se the Oracle driver in the Database sources, but we could not create any connection.
- We have even tried an 10G version of Oracle with the same result.

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

I have posted this issue on several forums including Oracles site. We are going to leave that database which demand linked servers to Oracle on the 32 bit server. I am going to install SQL Server 2005 on that server later. But I am still hoping for some solution to this delicate problem.

|||On 64 bit Windows you have seperate 32 bit and 64 bit ODBC worlds. There is no thunking between 32 and 64 bit components as there was between 16 and 32 bit. To work with 32 bit drivers you need to run the 32 bit ODBC administrator C:\WINDOWS\SysWOW64\odbcad32.exe|||

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I managed fixing thanks to tips from Ron (http://www.dbforums.com/showthread.php?p=6242791#post6242791) by downloading and installning "Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows (x64)" from Oracle download page.

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

Then I tried installong the 32 bit version. After that couldn't I see the odbc driver for Oracle in 64 bit odbc. At that time could I however fix my odbc source by running the 32 version av odbc! C:\Windows\SysWOW64\odbcad32.exe

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

I deinstalled Oracle 32 bit, downloaded x64 version av Oracle (after a tip from some one in the forum). The 64 bit version which I tried to install was for Itanium. I had AMD! I manage downloading X64 which is for AMD ("Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows (x64)") from Oracle download page.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

I did not install the Oracle database however. I ran the custom installation together with our Oracle expert. We insatalled ODBC, Oledb, ... After that I could find both drivers under the 64 bit version of ODBC and under linked servers in SQL Server 2005.

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

1. Install 64-bit 10.2.0.1 Database into OraDb_home.
2. Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home.
3. Apply patch 5500954 for 64-bit - this is a Critical Bug patch 8 for 64-bit.
4. Apply patch 5337014 for 32-bit - this is a patchset to upgrade the Client to 10.0.2.0.3.
5. Contrary to what Oracle note 334528.1 says (which is to patch the 64-bit Server), you must patch/upgrade the 32-bit client as well (otherwise, it still fails).

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

Lastly, the note above describing the use of the 32-bit 10.3.0.1 does NOT note that there has turned out to be a bug in the sqora32.dll ODBC driver (and two others). This can be fixed by patch 5699495 from MetaLink - metalink.oracle.com.

- Will

|||

Dear Sir,

I have ASP v3 installed on Windows Server 2003 x64 which is connected to Oracle9i Database server. As you know ASP works under 32 bit Tech. So i need to install 32bit oracle client on the server to let ASP connect to Oracle. I tried to install oracle9i client but it didnot work ( the ODBC test connection works but the ASP cannot connect). I Also tried ODBC 32-bit Driver & ODAC all-in-one for 64bit & InstantClient .... all of the failed.

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek

|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Wednesday, March 7, 2012

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle Linked Server Error & ODBC Drivers Missing

I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When using the Microsoft OLEDB Oracle driver I get the following error
Error 7399: OLE DB Provider 'MSDAORA' reported and error
OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:
The native Oracle driver seems to work ok, and I can also connect usine Orcale SQL+ untility just fine, but of course the native driver requires the older flavor of SQL which means that all of my currently coded SPs would have to be ported. The same setup works fine from another SQL2k server using the MS ODBC Oracle driver so I'd like to try using that, however the ODBC driver don't appear on the drop down list of driver in EM linked server screen! I tried installing MDAC 2.8 but it doesn't give me any options for installing the MS ODBC drivers?...Thanks in advance for any ideas on how to resolve this!..."Joe Loyd" <joe.loyd@.us.didata.com> wrote in message
news:570D6F0B-C76B-46FC-BF45-22D47A37128E@.microsoft.com...
> I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When
using the Microsoft OLEDB Oracle driver I get the following error:
> Error 7399: OLE DB Provider 'MSDAORA' reported and error.
> OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ]
> The native Oracle driver seems to work ok, and I can also connect usine
Orcale SQL+ untility just >fine, but of course the native driver requires
the older flavor of SQL which means that all of my >currently coded SPs
would have to be ported. The same setup works fine from another SQL2k
>server using the MS ODBC Oracle driver so I'd like to try using that,
however the ODBC driver don't >appear on the drop down list of driver in EM
linked server screen! I tried installing MDAC 2.8 but it >doesn't give me
any options for installing the MS ODBC drivers?...Thanks in advance for any
ideas >on how to resolve this!...
You want to use the MSDAORA OleDb driver, and MDAC installs it.
It looks like it's having trouble loading the Oracle OCI libraries. If the
Oracle Client is properly installed, this is usually either a permissions
problem or a path problem. Make sure that the [oracle home]\bin is in the
path. If you have to add it, or you installed the Oracle client since your
last reboot, you must reboot the server before the path change will take
effect for services (like SqlServer).
And make sure that the account running SQL has rights to the Oracle home.
David

Oracle Linked Server Error & ODBC Drivers Missing

I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When using the Microsoft OLEDB Oracle driver I get the following error:
Error 7399: OLE DB Provider 'MSDAORA' reported and error.
OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ]
The native Oracle driver seems to work ok, and I can also connect usine Orcale SQL+ untility just fine, but of course the native driver requires the older flavor of SQL which means that all of my currently coded SPs would have to be ported. The same setup
works fine from another SQL2k server using the MS ODBC Oracle driver so I'd like to try using that, however the ODBC driver don't appear on the drop down list of driver in EM linked server screen! I tried installing MDAC 2.8 but it doesn't give me any op
tions for installing the MS ODBC drivers?...Thanks in advance for any ideas on how to resolve this!...
"Joe Loyd" <joe.loyd@.us.didata.com> wrote in message
news:570D6F0B-C76B-46FC-BF45-22D47A37128E@.microsoft.com...
> I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When
using the Microsoft OLEDB Oracle driver I get the following error:
> Error 7399: OLE DB Provider 'MSDAORA' reported and error.
> OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ]
> The native Oracle driver seems to work ok, and I can also connect usine
Orcale SQL+ untility just >fine, but of course the native driver requires
the older flavor of SQL which means that all of my >currently coded SPs
would have to be ported. The same setup works fine from another SQL2k
>server using the MS ODBC Oracle driver so I'd like to try using that,
however the ODBC driver don't >appear on the drop down list of driver in EM
linked server screen! I tried installing MDAC 2.8 but it >doesn't give me
any options for installing the MS ODBC drivers?...Thanks in advance for any
ideas >on how to resolve this!...
You want to use the MSDAORA OleDb driver, and MDAC installs it.
It looks like it's having trouble loading the Oracle OCI libraries. If the
Oracle Client is properly installed, this is usually either a permissions
problem or a path problem. Make sure that the [oracle home]\bin is in the
path. If you have to add it, or you installed the Oracle client since your
last reboot, you must reboot the server before the path change will take
effect for services (like SqlServer).
And make sure that the account running SQL has rights to the Oracle home.
David

Oracle Linked Server Error & ODBC Drivers Missing

I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When using
the Microsoft OLEDB Oracle driver I get the following error:
Error 7399: OLE DB Provider 'MSDAORA' reported and error.
OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize r
eturned 0x80004005: ]
The native Oracle driver seems to work ok, and I can also connect usine Orca
le SQL+ untility just fine, but of course the native driver requires the old
er flavor of SQL which means that all of my currently coded SPs would have t
o be ported. The same setup
works fine from another SQL2k server using the MS ODBC Oracle driver so I'd
like to try using that, however the ODBC driver don't appear on the drop dow
n list of driver in EM linked server screen! I tried installing MDAC 2.8 but
it doesn't give me any op
tions for installing the MS ODBC drivers?...Thanks in advance for any ideas
on how to resolve this!..."Joe Loyd" <joe.loyd@.us.didata.com> wrote in message
news:570D6F0B-C76B-46FC-BF45-22D47A37128E@.microsoft.com...
> I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When
using the Microsoft OLEDB Oracle driver I get the following error:
> Error 7399: OLE DB Provider 'MSDAORA' reported and error.
> OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize[/vbc
ol]
returned 0x80004005: ][vbcol=seagreen]
> The native Oracle driver seems to work ok, and I can also connect usine
Orcale SQL+ untility just >fine, but of course the native driver requires
the older flavor of SQL which means that all of my >currently coded SPs
would have to be ported. The same setup works fine from another SQL2k
>server using the MS ODBC Oracle driver so I'd like to try using that,
however the ODBC driver don't >appear on the drop down list of driver in EM
linked server screen! I tried installing MDAC 2.8 but it >doesn't give me
any options for installing the MS ODBC drivers?...Thanks in advance for any
ideas >on how to resolve this!...
You want to use the MSDAORA OleDb driver, and MDAC installs it.
It looks like it's having trouble loading the Oracle OCI libraries. If the
Oracle Client is properly installed, this is usually either a permissions
problem or a path problem. Make sure that the [oracle home]\bin is in t
he
path. If you have to add it, or you installed the Oracle client since your
last reboot, you must reboot the server before the path change will take
effect for services (like SqlServer).
And make sure that the account running SQL has rights to the Oracle home.
David

Oracle linked server : won't run if driver AllowInProc is off

I've tested a link server from msde to oracle on a win2k server test machine
and after some effort, it worked properly. I noted down the steps i took to
get it to work. I could enable or disable AllowInProc for the Microsoft OLE
DB Oracle driver i was using to interface with Oracle. This machine is in a
test domain.
Now i wanted to configure the same thing on a server in our real domain.
That server is a true SQL server and is running multiple instances. The
thing is, i can't seem to get it to work if i disable the driver's
AllowInProc. When i disable AllowInProc i get this error mesage when trying
to see the tables of the linked server using Enterprise Manager:
Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
returned 0x80070005: Access denier.].
I tried reinstalling the latest MDAC (2.8) and rebooting the server
afterwards but it didn't change anything. I checked the Microsoft article
280106 and tried
If anybody has any ideas on this i'd be happy to read about them since i am
kind of running out of ideas right now...After much screaming and crying i found the potential cause. It seems the
driver doesn't receive the domain login hence returning "Access Denied". We
made a regular sql login and when used, everything worked perfectly. Any
idea if there are some services related to the driver or the oracle stuff
that might need to run under a different user account ?
Gonna try using odbc instead...
I'm posting all this in the hope that someone might help me or that it might
help someone.
"/dev/null" wrote:
> I've tested a link server from msde to oracle on a win2k server test machine
> and after some effort, it worked properly. I noted down the steps i took to
> get it to work. I could enable or disable AllowInProc for the Microsoft OLE
> DB Oracle driver i was using to interface with Oracle. This machine is in a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when trying
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i am
> kind of running out of ideas right now...|||Why don't you use oracle driver instead? You can install ORACLE client on
the SQL box and you should be able to get it working with no issues.
HTH
"/dev/null" </dev/null@.discussions.microsoft.com> wrote in message
news:9B5EF51E-2CBA-4B2F-8841-0A400396288A@.microsoft.com...
> I've tested a link server from msde to oracle on a win2k server test
machine
> and after some effort, it worked properly. I noted down the steps i took
to
> get it to work. I could enable or disable AllowInProc for the Microsoft
OLE
> DB Oracle driver i was using to interface with Oracle. This machine is in
a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when
trying
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i
am
> kind of running out of ideas right now...|||"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> Why don't you use oracle driver instead? You can install ORACLE client on
> the SQL box and you should be able to get it working with no issues.
>
The Oracle Client is required for MSDAORA as well, so it is already
installed on the box. I assume that the OP is uncomfortable running the
Oralce OleDb drivers and the Oracle Client inside the SqlServer address
space for fear it could crash Sql Server.
And quite honestly, I would not allow ad-hoc queries to an Oracle linked
server with the Oracle Client in-process in a production Sql Server. With
canned queries and testing, ok, but it's still a concern.
David|||We don't want to run the driver in process for the reason you thought: sql
server crashes = we better run damn fast for our lives.
It's not that i didn't try the driver provided by oracle but i never got it
to work. It seemed even more trouble than the microsoft driver. Even then i
suspect we'd have to be running it in-process.
"David Browne" wrote:
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > Why don't you use oracle driver instead? You can install ORACLE client on
> > the SQL box and you should be able to get it working with no issues.
> >
> The Oracle Client is required for MSDAORA as well, so it is already
> installed on the box. I assume that the OP is uncomfortable running the
> Oralce OleDb drivers and the Oracle Client inside the SqlServer address
> space for fear it could crash Sql Server.
> And quite honestly, I would not allow ad-hoc queries to an Oracle linked
> server with the Oracle Client in-process in a production Sql Server. With
> canned queries and testing, ok, but it's still a concern.
> David
>
>|||I just tested using an ODBC data source using the Microsoft ole db for Oracle
driver and it's the same issue. Is it because we set up SQL server to run
under a specific user account (not localsystem or whatever the default is) ?
the linked server was set up to use an odbc source, a source which used the
same driver as before...
and the fun won't stop!
"/dev/null" wrote:
> We don't want to run the driver in process for the reason you thought: sql
> server crashes = we better run damn fast for our lives.
> It's not that i didn't try the driver provided by oracle but i never got it
> to work. It seemed even more trouble than the microsoft driver. Even then i
> suspect we'd have to be running it in-process.
> "David Browne" wrote:
> > "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> > news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > > Why don't you use oracle driver instead? You can install ORACLE client on
> > > the SQL box and you should be able to get it working with no issues.
> > >
> >
> > The Oracle Client is required for MSDAORA as well, so it is already
> > installed on the box. I assume that the OP is uncomfortable running the
> > Oralce OleDb drivers and the Oracle Client inside the SqlServer address
> > space for fear it could crash Sql Server.
> >
> > And quite honestly, I would not allow ad-hoc queries to an Oracle linked
> > server with the Oracle Client in-process in a production Sql Server. With
> > canned queries and testing, ok, but it's still a concern.
> >
> > David
> >
> >
> >|||I think you hit the problem. I was having the same issues until I decided to
test it under the local system account instead of the domain account my SQL
Server was originally set.
Under the local system account, I had no problems setting a linked Oracle
server, in proc or out of proc, but when SQL Server is running under the
domain account, the connection doesn't work.
I've try to place the domain account in my local adminstrator group, but
that doesn't work either.
I've left my SQL Server running under my local system account for now, if
someone knows a fix for this issue, please post it.
H.Rosental
"/dev/null" wrote:
> I just tested using an ODBC data source using the Microsoft ole db for Oracle
> driver and it's the same issue. Is it because we set up SQL server to run
> under a specific user account (not localsystem or whatever the default is) ?
> the linked server was set up to use an odbc source, a source which used the
> same driver as before...
> and the fun won't stop!
> "/dev/null" wrote:
> > We don't want to run the driver in process for the reason you thought: sql
> > server crashes = we better run damn fast for our lives.
> >
> > It's not that i didn't try the driver provided by oracle but i never got it
> > to work. It seemed even more trouble than the microsoft driver. Even then i
> > suspect we'd have to be running it in-process.
> >
> > "David Browne" wrote:
> >
> > > "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> > > news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > > > Why don't you use oracle driver instead? You can install ORACLE client on
> > > > the SQL box and you should be able to get it working with no issues.
> > > >
> > >
> > > The Oracle Client is required for MSDAORA as well, so it is already
> > > installed on the box. I assume that the OP is uncomfortable running the
> > > Oralce OleDb drivers and the Oracle Client inside the SqlServer address
> > > space for fear it could crash Sql Server.
> > >
> > > And quite honestly, I would not allow ad-hoc queries to an Oracle linked
> > > server with the Oracle Client in-process in a production Sql Server. With
> > > canned queries and testing, ok, but it's still a concern.
> > >
> > > David
> > >
> > >
> > >

Oracle linked server : won't run if driver AllowInProc is off

I've tested a link server from msde to oracle on a win2k server test machine
and after some effort, it worked properly. I noted down the steps i took to
get it to work. I could enable or disable AllowInProc for the Microsoft OLE
DB Oracle driver i was using to interface with Oracle. This machine is in a
test domain.
Now i wanted to configure the same thing on a server in our real domain.
That server is a true SQL server and is running multiple instances. The
thing is, i can't seem to get it to work if i disable the driver's
AllowInProc. When i disable AllowInProc i get this error mesage when trying
to see the tables of the linked server using Enterprise Manager:
Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
returned 0x80070005: Access denier.].
I tried reinstalling the latest MDAC (2.8) and rebooting the server
afterwards but it didn't change anything. I checked the Microsoft article
280106 and tried
If anybody has any ideas on this i'd be happy to read about them since i am
kind of running out of ideas right now...After much screaming and crying i found the potential cause. It seems the
driver doesn't receive the domain login hence returning "Access Denied". We
made a regular sql login and when used, everything worked perfectly. Any
idea if there are some services related to the driver or the oracle stuff
that might need to run under a different user account ?
Gonna try using odbc instead...
I'm posting all this in the hope that someone might help me or that it might
help someone.
"/dev/null" wrote:

> I've tested a link server from msde to oracle on a win2k server test machi
ne
> and after some effort, it worked properly. I noted down the steps i took
to
> get it to work. I could enable or disable AllowInProc for the Microsoft O
LE
> DB Oracle driver i was using to interface with Oracle. This machine is in
a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when tryi
ng
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i a
m
> kind of running out of ideas right now...|||Why don't you use oracle driver instead? You can install ORACLE client on
the SQL box and you should be able to get it working with no issues.
HTH
"/dev/null" </dev/null@.discussions.microsoft.com> wrote in message
news:9B5EF51E-2CBA-4B2F-8841-0A400396288A@.microsoft.com...
> I've tested a link server from msde to oracle on a win2k server test
machine
> and after some effort, it worked properly. I noted down the steps i took
to
> get it to work. I could enable or disable AllowInProc for the Microsoft
OLE
> DB Oracle driver i was using to interface with Oracle. This machine is in
a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when
trying
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i
am
> kind of running out of ideas right now...|||"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> Why don't you use oracle driver instead? You can install ORACLE client on
> the SQL box and you should be able to get it working with no issues.
>
The Oracle Client is required for MSDAORA as well, so it is already
installed on the box. I assume that the OP is uncomfortable running the
Oralce OleDb drivers and the Oracle Client inside the SqlServer address
space for fear it could crash Sql Server.
And quite honestly, I would not allow ad-hoc queries to an Oracle linked
server with the Oracle Client in-process in a production Sql Server. With
canned queries and testing, ok, but it's still a concern.
David|||We don't want to run the driver in process for the reason you thought: sql
server crashes = we better run damn fast for our lives.
It's not that i didn't try the driver provided by oracle but i never got it
to work. It seemed even more trouble than the microsoft driver. Even then
i
suspect we'd have to be running it in-process.
"David Browne" wrote:

> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> The Oracle Client is required for MSDAORA as well, so it is already
> installed on the box. I assume that the OP is uncomfortable running the
> Oralce OleDb drivers and the Oracle Client inside the SqlServer address
> space for fear it could crash Sql Server.
> And quite honestly, I would not allow ad-hoc queries to an Oracle linked
> server with the Oracle Client in-process in a production Sql Server. With
> canned queries and testing, ok, but it's still a concern.
> David
>
>|||I just tested using an ODBC data source using the Microsoft ole db for Oracl
e
driver and it's the same issue. Is it because we set up SQL server to run
under a specific user account (not localsystem or whatever the default is) ?
the linked server was set up to use an odbc source, a source which used the
same driver as before...
and the fun won't stop!
"/dev/null" wrote:
[vbcol=seagreen]
> We don't want to run the driver in process for the reason you thought: sql
> server crashes = we better run damn fast for our lives.
> It's not that i didn't try the driver provided by oracle but i never got i
t
> to work. It seemed even more trouble than the microsoft driver. Even the
n i
> suspect we'd have to be running it in-process.
> "David Browne" wrote:
>|||I think you hit the problem. I was having the same issues until I decided to
test it under the local system account instead of the domain account my SQL
Server was originally set.
Under the local system account, I had no problems setting a linked Oracle
server, in proc or out of proc, but when SQL Server is running under the
domain account, the connection doesn't work.
I've try to place the domain account in my local adminstrator group, but
that doesn't work either.
I've left my SQL Server running under my local system account for now, if
someone knows a fix for this issue, please post it.
H.Rosental
"/dev/null" wrote:
[vbcol=seagreen]
> I just tested using an ODBC data source using the Microsoft ole db for Ora
cle
> driver and it's the same issue. Is it because we set up SQL server to run
> under a specific user account (not localsystem or whatever the default is)
?
> the linked server was set up to use an odbc source, a source which used th
e
> same driver as before...
> and the fun won't stop!
> "/dev/null" wrote:
>

Oracle linked server : won't run if driver AllowInProc is off

I've tested a link server from msde to oracle on a win2k server test machine
and after some effort, it worked properly. I noted down the steps i took to
get it to work. I could enable or disable AllowInProc for the Microsoft OLE
DB Oracle driver i was using to interface with Oracle. This machine is in a
test domain.
Now i wanted to configure the same thing on a server in our real domain.
That server is a true SQL server and is running multiple instances. The
thing is, i can't seem to get it to work if i disable the driver's
AllowInProc. When i disable AllowInProc i get this error mesage when trying
to see the tables of the linked server using Enterprise Manager:
Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
returned 0x80070005: Access denier.].
I tried reinstalling the latest MDAC (2.8) and rebooting the server
afterwards but it didn't change anything. I checked the Microsoft article
280106 and tried
If anybody has any ideas on this i'd be happy to read about them since i am
kind of running out of ideas right now...
After much screaming and crying i found the potential cause. It seems the
driver doesn't receive the domain login hence returning "Access Denied". We
made a regular sql login and when used, everything worked perfectly. Any
idea if there are some services related to the driver or the oracle stuff
that might need to run under a different user account ?
Gonna try using odbc instead...
I'm posting all this in the hope that someone might help me or that it might
help someone.
"/dev/null" wrote:

> I've tested a link server from msde to oracle on a win2k server test machine
> and after some effort, it worked properly. I noted down the steps i took to
> get it to work. I could enable or disable AllowInProc for the Microsoft OLE
> DB Oracle driver i was using to interface with Oracle. This machine is in a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when trying
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i am
> kind of running out of ideas right now...
|||Why don't you use oracle driver instead? You can install ORACLE client on
the SQL box and you should be able to get it working with no issues.
HTH
"/dev/null" </dev/null@.discussions.microsoft.com> wrote in message
news:9B5EF51E-2CBA-4B2F-8841-0A400396288A@.microsoft.com...
> I've tested a link server from msde to oracle on a win2k server test
machine
> and after some effort, it worked properly. I noted down the steps i took
to
> get it to work. I could enable or disable AllowInProc for the Microsoft
OLE
> DB Oracle driver i was using to interface with Oracle. This machine is in
a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when
trying
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i
am
> kind of running out of ideas right now...
|||"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> Why don't you use oracle driver instead? You can install ORACLE client on
> the SQL box and you should be able to get it working with no issues.
>
The Oracle Client is required for MSDAORA as well, so it is already
installed on the box. I assume that the OP is uncomfortable running the
Oralce OleDb drivers and the Oracle Client inside the SqlServer address
space for fear it could crash Sql Server.
And quite honestly, I would not allow ad-hoc queries to an Oracle linked
server with the Oracle Client in-process in a production Sql Server. With
canned queries and testing, ok, but it's still a concern.
David
|||We don't want to run the driver in process for the reason you thought: sql
server crashes = we better run damn fast for our lives.
It's not that i didn't try the driver provided by oracle but i never got it
to work. It seemed even more trouble than the microsoft driver. Even then i
suspect we'd have to be running it in-process.
"David Browne" wrote:

> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> The Oracle Client is required for MSDAORA as well, so it is already
> installed on the box. I assume that the OP is uncomfortable running the
> Oralce OleDb drivers and the Oracle Client inside the SqlServer address
> space for fear it could crash Sql Server.
> And quite honestly, I would not allow ad-hoc queries to an Oracle linked
> server with the Oracle Client in-process in a production Sql Server. With
> canned queries and testing, ok, but it's still a concern.
> David
>
>
|||I just tested using an ODBC data source using the Microsoft ole db for Oracle
driver and it's the same issue. Is it because we set up SQL server to run
under a specific user account (not localsystem or whatever the default is) ?
the linked server was set up to use an odbc source, a source which used the
same driver as before...
and the fun won't stop!
"/dev/null" wrote:
[vbcol=seagreen]
> We don't want to run the driver in process for the reason you thought: sql
> server crashes = we better run damn fast for our lives.
> It's not that i didn't try the driver provided by oracle but i never got it
> to work. It seemed even more trouble than the microsoft driver. Even then i
> suspect we'd have to be running it in-process.
> "David Browne" wrote:
|||I think you hit the problem. I was having the same issues until I decided to
test it under the local system account instead of the domain account my SQL
Server was originally set.
Under the local system account, I had no problems setting a linked Oracle
server, in proc or out of proc, but when SQL Server is running under the
domain account, the connection doesn't work.
I've try to place the domain account in my local adminstrator group, but
that doesn't work either.
I've left my SQL Server running under my local system account for now, if
someone knows a fix for this issue, please post it.
H.Rosental
"/dev/null" wrote:
[vbcol=seagreen]
> I just tested using an ODBC data source using the Microsoft ole db for Oracle
> driver and it's the same issue. Is it because we set up SQL server to run
> under a specific user account (not localsystem or whatever the default is) ?
> the linked server was set up to use an odbc source, a source which used the
> same driver as before...
> and the fun won't stop!
> "/dev/null" wrote:

Saturday, February 25, 2012

Oracle Instant Client and DTS data transformation from Oracle

I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji
When YOU ran it, it loaded the drivers off of YOUR machine. When you had
SQLAgent run it, it could find the drivers on the server. Make sure that
the SQL Server host machine also has the drivers loaded and under the
Windows User account you are running the SQL Server service accounts under.
I'm not sure how you are going to do this since you are running the service
account under Local System.
Sincerely,
Anthony Thomas

"hji" <hji@.discussions.microsoft.com> wrote in message
news:9F55BBC9-E1DF-4351-B9D9-4B92FDE74421@.microsoft.com...
I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me
on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji

Oracle Instant Client and DTS data transformation from Oracle

I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me o
n
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hjiWhen YOU ran it, it loaded the drivers off of YOUR machine. When you had
SQLAgent run it, it could find the drivers on the server. Make sure that
the SQL Server host machine also has the drivers loaded and under the
Windows User account you are running the SQL Server service accounts under.
I'm not sure how you are going to do this since you are running the service
account under Local System.
Sincerely,
Anthony Thomas
"hji" <hji@.discussions.microsoft.com> wrote in message
news:9F55BBC9-E1DF-4351-B9D9-4B92FDE74421@.microsoft.com...
I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me
on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji

Oracle Instant Client and DTS data transformation from Oracle

I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hjiWhen YOU ran it, it loaded the drivers off of YOUR machine. When you had
SQLAgent run it, it could find the drivers on the server. Make sure that
the SQL Server host machine also has the drivers loaded and under the
Windows User account you are running the SQL Server service accounts under.
I'm not sure how you are going to do this since you are running the service
account under Local System.
Sincerely,
Anthony Thomas
"hji" <hji@.discussions.microsoft.com> wrote in message
news:9F55BBC9-E1DF-4351-B9D9-4B92FDE74421@.microsoft.com...
I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me
on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji

Oracle drivers in connection managers

I am not seeing an option to use the OraOLEDB.Oracle driver when defining connections in ssis. This driver shows up in management studio when trying to create linked servers so I don't know why it wouldn't show up here. All the client tools are installed fine and the linked server works. Any ideas?This might be a novice answer, but are you sure the actual OLE driver is installed? I'm no Oracle expert by far, but the client and the actual driver that my Oracle dba gives for sql server installation are on seperate disks.|||Yes, the oledb drivers are installed. One important thing I failed to mention is that this is a 64 bit server. What appears to be happening is that even though we can create a linked server with the 64 bit oracle oledb driver, when you create a package in ssis it only lists the 32 bit native oledb drivers. I confirmed this by installing the 32 bit oracle client stuff as well and now the oracle oledb driver is listed in the drop downs. This seems a little absurd that ssis would only use the 32 bit drivers, can anyone explain this?|||

"The 32-bit SSIS Designer displays only 32-bit providers that are installed on the local computer. If you want to select a specific 64-bit provider to configure a connection manager, you must have the 32-bit version of the provider installed. However, you can still run the package in 64-bit mode in the development environment. Because the 32-bit and 64-bit versions of a provider have the same ID, the runtime selects the appropriate version to use based on the value of the Run64BitRuntime project property, which by default is True."

Found in this article....

http://msdn2.microsoft.com/en-us/library(d=robot)/ms141766.aspx

|||

Thanks for the info, that's a big help.

I understand why they did this but just like every other weird x64 quirk you have to spend hours digging through various documentation sources to even find a mention of it. Please just give us a comprehensive x64 guide instead of spreading stuff out in a thousand different places.

Friday, March 9, 2012

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Wednesday, March 7, 2012

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle Linked Server Error & ODBC Drivers Missing

Oracle Linked Server Error & ODBC Drivers Missing

Oracle Linked Server Error & ODBC Drivers Missing

Oracle linked server : won't run if driver AllowInProc is off

Oracle linked server : won't run if driver AllowInProc is off

Oracle linked server : won't run if driver AllowInProc is off

Saturday, February 25, 2012

Oracle Instant Client and DTS data transformation from Oracle

Oracle Instant Client and DTS data transformation from Oracle

Oracle Instant Client and DTS data transformation from Oracle

Oracle drivers in connection managers

Oracle DBA to learn MS SQL

Blog Archive

About Me