SQL Server Performance

connecting to linked servers.....urgent help neede

Discussion in 'General Developer Questions' started by avanthg, Mar 9, 2006.

  1. avanthg New Member

    hi i created a linked server(oracle) with the sql server 2000.
    I used
    EXEC sp_addlinkedserver
    @server = 'tprod123',
    @srvproduct = 'Oracle',
    @provider = 'MSDAORA',
    @datasrc = 'tprod123'
    Here the name of the database is 'tprod123' and i used the same name as the linked server name.
    then
    i used
    EXEC sp_addlinkedsrvlogin 'tprod123', 'false', 's', 'u', 'p'
    s is my sqlserverlogin
    uis the username and p is the password

    when i connect through this way i can see the tables and vies in the enterprise manager

    Now i was using
    select * from tprod123..mary.t1

    mary-the owner of the table t1.
    t1-table in oracle db

    when i run this select statement it is giving an error message of
    Server: Msg 7314, Level 16, State 1, Line 1
    OLE DB provider 'tprod123' does not contain table '"mary"."t1"'. 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='tprod123', TableName='"mary"."t1"'].

    and when i use the openquery it is working fine
    so can anyone tell me how to use the four letter naming convention for connecting to linked server.

    please help me out regarding this..
    Any help will be thankful




  2. satya Moderator

    Take help of KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;280106 about issues of Linked server with Oracle.
    so can anyone tell me how to use the four letter naming convention for connecting to linked server.
    Please refer to the SQL Server books online that is installed along with SQL tools about the syntax and code examples.



    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. ghemant Moderator

    quote:Now i was using
    select * from tprod123..mary.t1

    mary-the owner of the table t1.
    t1-table in oracle db

    when i run this select statement it is giving an error message of
    Server: Msg 7314, Level 16, State 1, Line 1
    OLE DB provider 'tprod123' does not contain table '"mary"."t1"'. 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='tprod123', TableName='"mary"."t1"'].

    if Mary is a table owner then is your syntax of query correct ?

    select * from [tprod123].[tprod123].[mary].t1 , isn't it ?

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  4. avanthg New Member

    thank u satya and ghemant
    i checkout the link and found the owner or the schema should follow the case convention when using four naming convention.
    thank you very much.

Share This Page