Provider Does Not Expose The Necessary Interfaces to Use a Catalog or Schema

When I execute SQL query on Amazon Redshift database using Linked Server connection on SQL Server Management Studio, following error occurred: Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema

In this SQL Server tutorial, I want to show how OLE DB provider MSDASQL options can be configured to resolve this error message for database administrator and SQL developers. Just to note, I experienced this Linked Server error on a SQL Server 2016 database instance.

I have created an Linked Server connection to Amazon Redshift database on SQL Server as shown in SQL Server tutorial Connect to Amazon Redshift from SQL Server using Linked Server with ODBC Data Source. On SQL Server Management Studio, I can easily display Linked Server connection and database tables without an issue as follows.

SQL Server Linked Server connection to Amazon Redshift database

Using SQL editor, I can query remote data source, using below SQL query.


EXEC ('select id, city from [public].[city] ') AT [REDSHIFT]

Code

query Redshift database from SQL Server using Linked Server

But when I directly query the Amazon Redshift database table using Linked Server connection as follows:


SELECT id, city FROM [REDSHIFT].[cdlredshiftdbcustom].[public].[city]

Code

I got below error message

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "MSDASQL" for linked server "REDSHIFT" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 3
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "REDSHIFT". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

To resolve this error on SQL Server Management Studio, connect to related SQL Server instance.
Drill through the following path on Object Explorer window:
Server Objects > Linked Servers > Providers
Right click on provider named MSDASQL and display properties.

SQL Server Linked Server providers including MSDASQL

Clear the checkbox next to "Level zero only"

SQL Server Linked Server MSDASQL provider properties

After the modification is done on the Linked Server provider, when I execute the same SQL query


SELECT id, city FROM [REDSHIFT].[cdlredshiftdbcustom].[public].[city]

Code

I can get the query results as expected successfully.

SQL query on Amazon Redshift database tables using SQL Server Linked Server

I hope this OLE DB provider option change solves your problems too.