Link an Oracle DB to SQL Server

I am having trouble trying to link an Oracle DB to my SQL Server. I get this error:
"cannot create an instance of oraoledb.oracle"
I created a DSN that connects to it just fine, but when I make the connection in SQL Server I keep getting this error. Any help is appreciated. I'm using SQL Server 2008 R2 and Oracle 11g.
dodgerfanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>cannot create an instance of oraoledb.oracle
means normally that the oraoledb is not properly installed, or is not configured yet with the oracle home ...  or you have a 32bit vs 64bit mismatch ...
0
dodgerfanAuthor Commented:
What can you do for the 32bit v. 64 bit mismatch?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will have to make sure that the sql server installation "bit version" is the same as the oracle client "bit version".
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dodgerfanAuthor Commented:
I verified that they are both 64 bit. I ensured Oracle home is configured. I am getting this error now:
"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
The ODBC connection is set up and connects when I test it. Once i create the linked server, I get this error when I test the connection. Am I missing something else?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show how exactly you configured the linked server?
0
dodgerfanAuthor Commented:
I used the sp_addlinkedserver procedure.
EXEC sp_addlinkedserver  
   @server='DevServ',
   @srvproduct='Oracle',
   @provider='MSDASQL',
   @datasrc='MyDB';

The this:
EXEC sp_addlinkedsrvlogin
@rmtsrvname=  'DevServ' ,
 @useself= 'FALSE',
 @locallogin=  NULL',
 @rmtuser=   'DBUser' ,
 @rmtpassword=   'Test' ;

It creates it, then when you test you get the error.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.