I ran into an issue creating a linked server on SQL 2005 64-bit. I was trying to connect to a 2000 32-bit server. The error I received was:
OLE DB provider “SQLNCLI” for linked server “servername” returned message “Unspecified error”.
OLE DB provider “SQLNCLI” for linked server “servername” 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 1
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI” for linked server “servername”. The provider supports the interface, but returns a failure code when it is used.
To fix this error you need to run the instcat.sql file on the 2000 instance. This file is in SP4. This file adds a missing stored procedure to the 2000 instance.