asked on

Failed to retrieve data for this request. (Linked server from Microsoft SQL2005 to Oracle 10g)

I have successfully created a linked server from Microsoft SQL2005 to Oracle Database but having issue browsing the Catalogs. I was wondering if anyone have the similar issue like this? Is this the security set up on Oracle Database? I have no control over the Oracle Database so if that's the issue then I can contact the Oracle DBA to give me the permission.

Here is the error message I got!

-----------------------------------------------------------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

Additional information:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "SQL Server" for linked server "(null)" reported an error. One or more arguments were reported invalid by the provider.

Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB Provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when is used. (Microsoft SQL Server, Error: 7399)

----------------------------------------------------------------

I tested the connection and it is okay and I just can not browse the Catalogs.

60MXG

ASKER

It looks like 'select * from openquery(linked_server, 'select ....')', check books online for details. is my testing query for the table access but receiving error too!

ASKER CERTIFIED SOLUTION

myrotarycar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

60MXG

ASKER

It was the SQL Syntax caused this error!