SQL SERVER – Fix : Error : Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset DBSCHEMA_TABLES_INFO for OLE DB provider SQLNCLI for linked server LinkedServerName

You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server or to a linked SQL Server 7.0 server.

Error:
The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI” for linked server “<LinkedServerName>”. The provider supports the interface, but returns a failure code when it is used.

Fix/WorkAround/Solution:

Use Windows Authentication mode
For a default instance
osql -E -S <LinkedServerName> -i <Location>\instcat.sql

For a named instance
osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql

Use SQL Server Authentication mode
For a default instance
osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName> -i <Location>\instcat.sql

For a named instance
osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql

By default, this folder is C:\Program Files\Microsoft SQL Server\MSSQL\Install.

Example:
osql -U sa -P MyPassWord -S Database.IpAddress.com -i “C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql”

Reference : Pinal Dave (https://blog.sqlauthority.com)