Results 1 to 9 of 9

Thread: Linked Server

  1. #1
    Join Date
    Feb 2009
    Posts
    9

    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

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Try this:

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

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

  3. #3
    Join Date
    Feb 2009
    Posts
    9
    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

  4. #4
    Join Date
    Feb 2009
    Posts
    9
    Thanks you.
    And how to map sql login to Oracle user in linked server?
    Please hlep me on this.

    Regards,
    Sowmya

  5. #5
    Join Date
    Feb 2009
    Posts
    9
    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

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Check syntax of openquery in sql books online. To map user, go to linked server properties -> security tab.

  7. #7
    Join Date
    Jun 2009
    Posts
    1

    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.

  8. #8
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Did you reboot SQL server after installing Oracle client?. It is strange but is required for the driver to work.

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    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
  •