Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server ""

  • 發行項

We have a Linked Server from SQL Server 2005 instance to SQL Server 2000 instance. The linked server works fine. However if we try to Query a Linked Server through Four-Part name then it fails with the below error message

SELECT *

FROM LinkServer.Northwind.dbo.Shippers

Error Message

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "<Linked Server Name>" reported an error. The provider did not give any information about the error.

Msg 7312, Level 16, State 1, Line 1

Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "<Linked Server Name>". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema..

Inorder to resolve this, check if "zero level only" property for SQLNCLI oledb provider is set to 0. If it

is 1 (or ON), 7399 error will occur. Make sure its unchecked.

To set the "zero level only" property, go to

SQL Management Studio-> "Server Object" -> "Linked Servers" -> "Provider"

right click on “SQLNCLI” and go to property and uncheck the “zero level only” property

click on “OK” and restart the SQL Server service.

clip_image002

You can also change this setting through registry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\SQLNCLI

LevelZeroOnly

If we set it to 1 then only level 0 OLE DB Providers are allowed. If it is 0 (default), all levels of OLE DB provider are allowed. So the value specifies whether all OLE DB providers are supported or just those that are compliant with the level 0 OLE DB interface.

The other workaround is to use OPENQuery instead of Four-Part Query

Comments

  • Anonymous
    September 18, 2009
    Faced similar issue and followed through the steps mentioned in the blog and it worked. Thanks

  • Anonymous
    December 30, 2009
    Thanks much! This worked for us as well!

  • Anonymous
    April 29, 2010
    Great.  Worked for me for access to sybase 12 from sql 2008 via linked server.

  • Anonymous
    July 20, 2010
    Although this solution may work for some, there is something else that can cause this error as well, which is not using all CAPS. Oracle sometimes requires you to use ALL CAPS as a 4 part qualifier. For example: LinkedServerName.CatalogName.Schema.TableName. Or you may not have a catalog name, which is not entirely uncommon and you can then do this: LINKEDSERVERNAME..SCHEMANAME.TABLENAME. Notice the two periods or the fact that the catalog name has been left out. Cheers.

  • Anonymous
    August 31, 2011
    Thank you !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  • Anonymous
    November 07, 2011
    Thx, it's working for OraOLEDB.Oracle ;) SELECT   *    FROM      OPENDATASOURCE(         'OraOLEDB.Oracle',         'Data Source=orcl_db_name;User ID=user;Password=pass'         )...dual

  • Anonymous
    January 31, 2012
    Thank you Dan, your comment was right on the dot!! Works now. Who could imagine catalog names are not used sometimes.

  • Anonymous
    May 18, 2012
    Thanks a lot.!!!! looking for long time... I will marry you!!!!

  • Anonymous
    March 05, 2014
    The comment has been removed