Struggling to link an oracle server

i am running a web site with full login etc powered using my sql server
we use an internal data system running on oracle... i need the data for the web from that oracle db

to run reports in crystal staff use 'oracle net manager' for their connection which lets them run their full info and get their reports.

i need to get this type of detail to correspond with my sql users etc so need to link the server in some way

when i go to linked servers NEW not sure what to use
should i use 'microsoft OLE DB provider for oracle' or 'oracle provider for ole db'

on both of these options i then get asked for
Product Name:
Data source:
Provider String:

not sure what i need for these, i have the db name, server name, server IP, username and password for the oracle db, just not sure what order and if i have all information.

also i read somewhere about having oracle installed on my sql server... if orable net manager can connect, should this not mean i can get sql to connect, or somehow use this oracle net manager to connect the linked server.

I have tried a connection specifying Product Name as the db name and the data source as the server name adding in a security user with the usernme and password.
When simply trying to view tables i get:
error 7399 OLE DB provider 'OraOLEDB.oracle' reported an error
OLE DB error trace [OLE/DB provider IDBInitialize: initialize returned 0x80004005:]

not sure where i'm going wrongor if i've just entered the wrong detail in each section??
bhendrickAsked:
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.

BrandonGalderisiCommented:
Have you installed the data driver for oracle 9i/10g?
0
bhendrickAuthor Commented:
not sure, where would i 1. find out if i have the driver installed, and 2. find the driver??
sorry, limited knowledge on oracle
0
BrandonGalderisiCommented:
ME too.  I struggled with this when I did it once.  Let me find it.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

BrandonGalderisiCommented:
Are you running 9i? 10g? other?
0
bhendrickAuthor Commented:
Its 10G the oracle client is running on the sql server and can connect directly through the oracle application as the listener is running fine.
0
BrandonGalderisiCommented:
What syntax did you use to add the linked server?
EXEC sp_addlinkedserver
    @server = 'LONDON Mktg',
    @srvproduct = 'Oracle',
    @provider = 'MSDAORA',
    @datasrc = 'MyServer'
GO

Open in new window

0
ThejakaCommented:
Try:

OLE DB provider: Microsoft OLE DB Provider for Oracle
Product Name: {Optional/Any}
Provider Name: MSDAORA
Datasource: SQL*Net alias for Oracle database


Or:

OLE DB provider: Oracle Provider for OLE DB
Product Name: {Optional/Any}
Provider Name: OraOLEDB.Oracle
Datasource: Alias for Oracle Database


Links:

http://technet.microsoft.com/en-us/library/ms190479.aspx
http://support.microsoft.com/kb/280106
http://www.sqlmag.com/Articles/ArticleID/49687/49687.html?Ad=1
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
bhendrickAuthor Commented:
gave good info that gave a more confident approach to the situation...

link fixed, thanyou
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 2005

From novice to tech pro — start learning today.