October 4, 2007 at 1:39 pm
i know it's installed automatically; but if thats the case why don't i see it
in my providers list??
Management Studio\Server Objects\Linked Servers\Providers
this is all i have under 'Providers'
ADsDSOObject
MSDAOSP
MSIDXS
MSOLAP
SQLNCLI
SQLOLEDB
SQLXMLOLEDB
SQLXMLOLEDB.4.0
i'm looking for this, but don't see it.
MSDAORA
any ideas about this?
_________________________
October 5, 2007 at 11:26 am
Download and install these drivers.
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
Oracle server 64 bit or 32 bit?
October 5, 2007 at 2:38 pm
it's an SQL 2005 64bit environment, but as to the Oracle environment i'm unaware of
what bit it it.
_________________________
October 8, 2007 at 12:46 pm
here's something interesting...
when looking at the registry i can see MSDAORA
'hkey_local_machine', 'software\microsoft\microsoft sql server\mssql.l\providers'
why does Management Studio not reflect it under the Providers list???
any ideas?
_________________________
October 8, 2007 at 9:20 pm
Watch yourself with this one, on a 64-bit machine you may well need to install both the 32 and 64 bit oracle drivers before you can see "it" everywhere including SSIS, linked servers, etc.
The MS Oracle driver (written by MS) was last updated for version 8.0 of Oracle and MS has stated that they're out of the Oracle driver business... there will not be updated or 64 bit versions of that driver. Newer versions of Oracle particularly 11 may not work correctly with a driver written for 8.0 (Oracle maintains compatibilitity for 2 versions...).
Joe
October 9, 2007 at 12:07 am
"...Oracle and MS has stated that they're out of the Oracle driver business... there will not be updated or 64 bit versions of that driver..."
do what now? i'm hard pressed to believe that.
_________________________
October 9, 2007 at 12:13 am
I should have clarified that, MS says to use the Oracle drivers provided by Oracle.
October 9, 2007 at 6:06 am
ahh... i understand.
however; for the moment MSDAORA is installed with SQL Server 2005, so do
you have any ideas about my original inquiry about why i can't see it in my providers
list?
_________________________
October 9, 2007 at 8:43 am
Yup, the core database engine is 64-bit, that particular driver is 32-bit only... what you're seeing is a list of available 64 bit drivers. Download and install the 64-bit oracle driver and it will appear in the list of available drivers for a linked server.
The older 32-bit driver is there, you can get at it with the 32-bit odbc admin tool but SSMS won't see it as it is looking only for 64 bit drivers.
October 9, 2007 at 8:49 am
great! thats the info i was looking for.
now i know where to start.
many thanks!
_________________________
October 19, 2008 at 9:13 pm
Hello:
I have been reading your string of emails for the Oracle 64 bit driver support for SQL Server 64 bit. I am having the same issue and it was resolved by installing the 64 bit Oracle client.
My next question to add to this is, would you know Oracle will support a 64 bit MSDAORA sql server connection to a 32 bit Oracle database server.
SQL Server MSDAORA 64 bit === > 32 bit Oracle DB (remote)
I cannot find these compatibility specs anywhere on line and need to confirm. I do not have a metalink account.
Thanks!
October 21, 2008 at 7:29 am
When you are going across the network, bit level doesn't really matter. The problems start when a program written for a bit level needs to import libraries on the machine its running on. It need the correct bit level libraries/dlls. You shouldn't experience any problems.
October 21, 2008 at 8:45 am
Perfect! Thank You!
September 23, 2010 at 5:02 am
is there a 64 bit MSDAORA driver available from MS yet?
i have been struggling with creating a linked server from SQL 2005 64 bit to an Oracle (9.2.0.4) database. i have found a workaround using the info in the below links but it's not pretty.
- Install 64Bit Oracle Client
- Install 64Bit Oracle OLEDB Provider
- Set the AllowInProcess value in the registry
- set up the linked server using oracle oledb provider
http://sqlservercorner.blogspot.com/2009/03/linked-servers-for-64-bit-sql-2005-sp3.html
- queries return error - Invalid data for type "numeric" for columns with numeric datatype. seems to be a problem with implicit numeric conversions. if you wrap the numeric in a function such as ROUND(), it works fine.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply