-
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.
-
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked serv
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|