Had an interesting issue recently with a linked server going from a 64 bit SQL2008R2 host to a 32 bit SQL2000 host.
The error message when running the distributed query was :-
OLE DB provider “SQLNCLI10” for linked server “sql02source” returned message “Unspecified error”.
OLE DB provider “SQLNCLI10” for linked server “sql02source” returned message “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 2
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10” for linked server “sql02source”. The provider supports the interface, but returns a failure code when it is used.
According to Microsoft, this problem occurs because the system stored procedures are not upgraded as part of the service pack installation. You must manually upgrade the system stored procedures after you install SQL Server 2000 Service Pack (SP3) or SQL Server 2000 Service Pack 4 (SP4) by running instcat.sql.
Official MS response is here :-
http://support.microsoft.com/default.as … -us;906954
Peace to all,