Linked Server

Hi,
I tried to create the linked server to get the oracle data in sql server 2005.So I created server as follows.
right clicked on the linked servers-> new linked server
and the details are
linked server name-> OrclDB
server type-> other data source
provider->Oracle Provider for OLE DB
product name-> oracle
data source->pasdb
provider string->microsoft oledb
and it's get conected (test connection)

And when I tried to query the following

select * from
[OrclDB].oracle.pasdb.[dbo.t_cmd_codn]

(linked_servername.catalog.schema.object_name)


But I am getting the error as

OLE DB provider "MSDAORA" for linked server "OrclDB" returned message "ORA-01004: default username feature not supported; logon denied
".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "OrclDB" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "OrclDB".


It will of very great help if any can provide the cause for this error and query to checkout the table.

Thanks in Advance,
Sowmya

Try this:

select * from operquery(OrclDB, 'select * from ...')

By the way, did you map sql login to Oracle user in linked server?

Thanks a lot!
But when I gave the query as
select * from openquery(OrclDB, 'select * from [TEST_SQL].[ASUP_HDD].[dbo].[t_scsi_cmd_checkCondition]')
I got the Error msg as

OLE DB provider "MSDAORA" for linked server "OrclDB" returned message "ORA-01004: default username feature not supported; logon denied
".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "OrclDB" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "OrclDB".

which I am not able to make it out.
COuld anyone please let me know the cause for the same which will of very great help.

Thanks,
Sowmya

Thanks you.
And how to map sql login to Oracle user in linked server?
Please hlep me on this.

Regards,
Sowmya

Hi, I tried with now with the modification of the src
select * from openquery (TEST_LNK,'select * from [ASUP_HDD].[dbo].[t_scsi_cmd_checkCondition]')
and I got the Err msg as

OLE DB provider "MSDAORA" for linked server "TEST_LNK" returned message "ORA-12154:
TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server
"TEST_LNK".

Could be helpful to me , if u let e know the cause for the same.

Thanks,
Sowmya

Check syntax of openquery in sql books online. To map user, go to linked server properties -> security tab.

Hi,

I faced the same problem when connecting from SQL 2005 server to Oracle. This issue could be solved by installing Oracle client(preferrabely version 8 OR above) on the machine on which the SQL Server is installed.

This is based on the assumption that
'Ad Hoc Distributed Queries' is configuered to have Config_Value as 1.

Did you reboot SQL server after installing Oracle client?. It is strange but is required for the driver to work.

skanyar posted solution for the issue I believe.