Results 1 to 6 of 6

Thread: Msg 7320

  1. #1
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38

    Unanswered: Msg 7320

    Hi,

    I've successfully created a connection (linked server) in SQL Server 2000 to an Oracle database (9i). I'm running SQL Server on a WIN2K machine.

    Now, I know that the connection is made because I can see the list of tables and views under my Linked Server.

    With that said, if I try ANY kind of query on these tables I get a Msg 7320 error. The name of my Linked Server is OracleSource. Here are some examples of queries that fail:

    select * from openquery(OracleSource, 'select * from ETA_USER.ABBR_DEVICE')
    Select * From OracleSource..ETA_USER.ABBR_DEVICE

    ETA_USER is the Schema and ABBR_DEVICE is the table name.

    They are the same except one is using OpenQuery and not the other. I know that this query works because I tried it successfully in SQL Plus.

    I have no idea what to try next and there doesn't seem to be any help on the net (I've already consulted KB articles 270119, 251238, 248156 and 280106)

    Any ideas, please!

    Thanks, Skip.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Have you tried it with the 4 part name?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38

    Question

    Originally posted by Brett Kaiser
    Have you tried it with the 4 part name?
    The second select is using four-part naming, no? I know I'm missing the catalog (between the server name and the schema) but I don't think there's one because sp_table_ex returned NULL for this table's catalog.

    Is there a way in SQL Plus to view a table's catalog if there's one?

    Thanks,

    Skip.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    What do you have selected under Security Tab int the linked server option in EM. I have had a problem where the Linked server to oracle did not work until i had the radio button

    Be made with this security context selected.

    Another Miracle ..... brett
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38
    Thanks Brett but I do need a security context because authentication is required to login to the remote server (a special user has been created for this purpose).

    Plus, the EXACT same query works under SQL Plus!

    Anyway, any other ideas???

    Skip

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Yup ..

    Thats what I am talking about ... The problem i faced a long time ago was ...
    I was able to run the query from SQL plus ... also from QA but not from a stored procedure ... and using the security context helped me out ... am not sure in your case what is the problem until you elaborate what all you have tried out
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •