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.Have you tried it with the 4 part name?|||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.|||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|||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|||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