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.You might be encountering a limitation as described in :
244661 INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
http://support.microsoft.com/?id=244661
You can also try dbcc trace (7300,3604) to see if you can get the provider
to return more information on why it is failing.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label successfully. Show all posts
Showing posts with label successfully. Show all posts
Monday, March 19, 2012
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
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
Subscribe to:
Posts (Atom)