and then trying to make sample select, I get the following:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. Authentication failed.
[OLE/DB provider returned message: ORA-01017: invalid username/password; logon denied]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
Please advice, where can I set up the username and password for my Oracle, so the things will work.
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'ORATEST' does not contain table 'deptsales'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='ORATEST', TableName='deptsales'].
I am sure that the table does exist, I could see it via Excel.
Let me explain further the steps that you needed to do before query the Oracle database.
1) You'll need to install the Oracle client software on the SQL Server,
and reboot the server for the Oracle's DLL to register.
2) Run the
sp_addlinkserver 'TestOracle', 'OLEOracle', 'MSOracle', 'TSTORA'
'TestOracle' is the linkserver name
'OLEOracle' is the OLE DB Name
'MSOracle' is the Microsoft's OLE DB
'TSTORA' is the Net Service Name (create with Oracle NetConfig
(Add "scott' with pwd as "tiger' on 'TestOracle', if * is set to TRUE means SQL Server will use the CURRENT login to connect to the link server, use "TRUE' only if the Logins of the 2 DB servers matched).