December 22, 2010 at 12:35 pm
I have a need to connect a linked server to an instance of Oracle 10g on my Windows 64-bit instance of SQL Server 2008. What I'm looking for is the ODBC drivers that will enable me to make a linked server connection. I keep getting pointed to Oracle's site to download a massive package that is apparently used to install an Oracle database instance -- not really what I wanted.
Can someone point me to a specific software package to install that will give me ODBC connectivity to Oracle without a bunch of overhead?
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
December 26, 2010 at 8:38 am
Aaron N. Cutshall (12/22/2010)
I have a need to connect a linked server to an instance of Oracle 10g on my Windows 64-bit instance of SQL Server 2008. What I'm looking for is the ODBC drivers that will enable me to make a linked server connection. I keep getting pointed to Oracle's site to download a massive package that is apparently used to install an Oracle database instance -- not really what I wanted.Can someone point me to a specific software package to install that will give me ODBC connectivity to Oracle without a bunch of overhead?
I think you are directed to the Oracle client which in Windows 2008 is a huge file but it is required for all development with Oracle. If all you need is the Linked server then use the Linked server component in SQL Server 2008 under server objects in SMS and use the predefined Oracle provider.
Kind regards,
Gift Peddie
December 27, 2010 at 6:55 am
Gift Peddie (12/26/2010)
If all you need is the Linked server then use the Linked server component in SQL Server 2008 under server objects in SMS and use the predefined Oracle provider.
That would be really nice to use if I had that available! All I have for providers are the following:
Microsoft OLE DB Provider for SQL Server
Microsoft OLE DB Provider for Analysis Services 10.0
OLE DB Provider for Microsoft Directory Services
SQL Server Native Client 10.0
Microsoft OLE DB Provider for ODBC Drivers
Microsoft OLE DB Simple Provider
Microsoft OLE DB Provider for Indexing Service
How do I get the Oracle connector? Any suggestions?
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
December 27, 2010 at 8:55 am
Then it must be edition related or an upgrade because there is MSDAORA in the list of providers in my developer editions for both 2005 and 2008. You need to either do a fresh install or run a repair.
Kind regards,
Gift Peddie
December 27, 2010 at 9:07 am
Gift Peddie (12/27/2010)
Then it must be edition related or an upgrade because there is MSDAORA in the list of providers in my developer editions for both 2005 and 2008. You need to either do a fresh install or run a repair.
That's interesting because I looked at my developer's edition on my workstation and it does indeed have that provider whereas the enterprise edition on my production server does not. I wonder if that's merely an option that needed to be checked during install and the IT folks just didn't think it was necessary. I'll have to investigate.
Have you successfully connected to an Oracle database using that provider? The instance I need to connect to is running on a Red Hat Linux box and I have no idea how to make that connection.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
December 27, 2010 at 9:25 am
Aaron N. Cutshall (12/27/2010)
Gift Peddie (12/27/2010)
Then it must be edition related or an upgrade because there is MSDAORA in the list of providers in my developer editions for both 2005 and 2008. You need to either do a fresh install or run a repair.That's interesting because I looked at my developer's edition on my workstation and it does indeed have that provider whereas the enterprise edition on my production server does not. I wonder if that's merely an option that needed to be checked during install and the IT folks just didn't think it was necessary. I'll have to investigate.
Have you successfully connected to an Oracle database using that provider? The instance I need to connect to is running on a Red Hat Linux box and I have no idea how to make that connection.
You could try creating the linked server without security context from your development box, if you run into issues then you need to install the Oracle client because the Oracle client is required for all development.
Kind regards,
Gift Peddie
December 27, 2010 at 10:02 am
I don't know what level of access you have on the server to verify this, but perhaps the latest version of MDAC needs to be installed or re-installed. You can try using the following query to return the version number for MDAC components and also perhaps the dll for Microsoft's Oracle driver, then compare the result between your local instance and the other server. You'll need VIEW SERVER STATE permission to run this, and the MDAC modules may not be loaded, and thus not returned, unless a linked server connection was used since the last server reboot.
select name, description, file_version, product_version
from sys.dm_os_loaded_modules
where name like '%msadc%' or name like '%oledb%' or name like '%MSDAORA%';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 27, 2010 at 10:31 am
Eric Russell 13013 (12/27/2010)
I don't know what level of access you have on the server to verify this, but perhaps the latest version of MDAC needs to be installed or re-installed. You can try using the following query to return the version number for MDAC components and also perhaps the dll for Microsoft's Oracle driver, then compare the result between your local instance and the other server. You'll need VIEW SERVER STATE permission to run this, and the MDAC modules may not be loaded, and thus not returned, unless a linked server connection was used since the last server reboot.
select name, description, file_version, product_version
from sys.dm_os_loaded_modules
where name like '%msadc%' or name like '%oledb%' or name like '%MSDAORA%';
MDAC is not relevant to Windows Vista, 7 and 2008 because all three comes with new data access Windows Data Access Components (WDAC).
Kind regards,
Gift Peddie
December 27, 2010 at 11:39 am
I did try the query on my Windows 7 workstation running the Developer's Edition and got the following results:
name description file_version product_version
C:\Program Files\Common Files\System\Ole DB\oledb32.dll NULL 6.1:7600.16385 6.1:7600.16385
C:\Program Files\Common Files\System\Ole DB\OLEDB32R.DLL OLE DB Core Services Resources 6.1:7600.16385 6.1:7600.16385
C:\Program Files\Common Files\System\msadc\msadce.dll NULL 6.1:7600.16385 6.1:7600.16385
C:\Program Files\Common Files\System\msadc\msadcer.dll OLE DB Cursor Engine Resources 6.1:7600.16385 6.1:7600.16385
C:\Program Files\Common Files\System\Ole DB\sqloledb.dll OLE DB Provider for SQL Server 6.1:7600.16385 6.1:7600.16385
C:\Program Files\Common Files\System\Ole DB\msdaora.dll OLE DB Provider for Oracle 6.1:7600.16385 6.1:7600.16385
C:\Program Files\Common Files\System\Ole DB\MSDAORAR.DLL OLE DB Provider for Oracle Resources 6.1:7600.16385 6.1:7600.16385
On the production server running Enterprise 2008 64-bit, I got only this:
name description file_version product_version
C:\Program Files\Common Files\System\Ole DB\oledb32.dll NULL 6.1:7600.16385 6.1:7600.16385
C:\Program Files\Common Files\System\Ole DB\OLEDB32R.DLL OLE DB Core Services Resources 6.1:7600.16385 6.1:7600.16385
C:\Program Files\Common Files\System\msadc\msadce.dll NULL 6.1:7600.16385 6.1:7600.16385
C:\Program Files\Common Files\System\msadc\msadcer.dll OLE DB Cursor Engine Resources 6.1:7600.16385 6.1:7600.16385
C:\Program Files\Common Files\System\Ole DB\sqloledb.dll OLE DB Provider for SQL Server 6.1:7600.16385 6.1:7600.16385
c:\Program Files\Microsoft Analysis Services\AS OLEDB\10\msolap100.dll Microsoft OLE DB Provider for Analysis Services 10.0 10.0:2531.0 10.0:2531.0
c:\Program Files\Microsoft Analysis Services\AS OLEDB\10\Resources\1033\msmdsrv.rll Microsoft SQL Server Analysis Services 10.0:2531.0 10.0:2531.0
In my research, I saw where some folks have stated that the Oracle provider is not available for 64-bit implementations that I have to install the Oracle development client. That seems to be a bit unwieldy just to have a Linked Server connection for a production server.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
December 27, 2010 at 12:02 pm
In my research, I saw where some folks have stated that the Oracle provider is not available for 64-bit implementations that I have to install the Oracle development client. That seems to be a bit unwieldy just to have a Linked Server connection for a production server.
That is not correct I run one of the busiest Oracle connection thread on the MSDN forums, the reason for the x86 provider is Visual Studio being only x86 used to develop x64/AnyCPU and x86 applications. One more thing Oracle now provides x64 client for Windows 2008 x64, in development you use both the x64 and x86 Oracle clients to avoid compatibility issues.
Kind regards,
Gift Peddie
December 27, 2010 at 12:04 pm
Dear Aaron N. Cutshall
Complementing the above Gift Peddie said that in addition to installing the Oracle client and configure the tnsnames.ora must add / configure a new dsn ODBC data sources through Microsoft's WOW64 which makes applications run in 32bit 64bit environments.
I went through this problem last year and decided this way.
Happy new year
Diêgo A R Miranda
MCDBA Database Administrator-Database Devoloper SQL SERVER 2000
MCTS: Database Administrator 2005
MCITP: Database Administrator 2005
December 27, 2010 at 12:25 pm
Gift Peddie (12/27/2010)
That is not correct I run one of the busiest Oracle connection thread on the MSDN forums, the reason for the x86 provider is Visual Studio being only x86 used to develop x64/AnyCPU and x86 applications. One more thing Oracle now provides x64 client for Windows 2008 x64, in development you use both the x64 and x86 Oracle clients to avoid compatibility issues.
So, the native Oracle provider that I see in my 32-bit environment should also be available to me in the 64-bit environment? Is that an option that I should find when repairing the SQL Server installation?
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
December 27, 2010 at 1:13 pm
Aaron N. Cutshall (12/27/2010)
Gift Peddie (12/27/2010)
That is not correct I run one of the busiest Oracle connection thread on the MSDN forums, the reason for the x86 provider is Visual Studio being only x86 used to develop x64/AnyCPU and x86 applications. One more thing Oracle now provides x64 client for Windows 2008 x64, in development you use both the x64 and x86 Oracle clients to avoid compatibility issues.So, the native Oracle provider that I see in my 32-bit environment should also be available to me in the 64-bit environment? Is that an option that I should find when repairing the SQL Server installation?
Microsoft choose not to pay for x64 MSDAORA driver however the MSDASQL is a generic x64 OLE DB provider for ODBC. You could try connecting with the MSDASQL, if you run into issues then you may have to install the Oracle client.
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
Kind regards,
Gift Peddie
December 28, 2010 at 4:08 am
Aaron
The detail is that some resources are running SSIS 32bit mode only, even if you are in 64bit environment.
So in this case after installing the Oracle client and configure the tnsnames.ora you must create the dsn using the driver MSDAORA.
Use the Application Data Sources (ODBC) (odbcad32.exe) that is in the directory C: \ WINDOWS \ SysWOW64
To use the WOW64 follow the guidelines of the following sites:
http://msdn.microsoft.com/en-us/library/aa384187 (v = VS.85). aspx
http://www.techsupportalert.com/content/how-windows7-vista64-support-32bit-applications.htm
Diêgo A R Miranda
MCDBA Database Administrator-Database Devoloper SQL SERVER 2000
MCTS: Database Administrator 2005
MCITP: Database Administrator 2005
December 28, 2010 at 6:15 am
Diego,
Thanks for the info. That does help.
Gift,
Your help has truly been a gift! I was successful in getting the Oracle client installed and managed to make a successful connection via SQL*Plus and a test connection via ODBC. Now, I just have to figure out how to make a Linked Server connection with the ODBC. Or is there a better way?
Aaron
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply