I am setting up linked server 2005 to connect to Unidata using ODBC connection. I used Management Studio to create the link. When I test, it connects successfully. However when I try to open the catalog, I get below error:
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 it is used. (Microsoft SQL Server, Error: 7399)
I am passing remote account. Attached is a s/n print of linked server setting.
Just so you know, when I use sp_tables_ex , it lists all my schemas. Also I can query some tables and not others!
The connections test is successful, BUT I can't expand the catalogs!!!!
Does CAD have permission on all tables? Do those table have data type not compatible with sql server? By the way, expand catalog doesn't work on some data source. Especially non-ole db providers.