If you encounter this error:
OLE DB provider "SQLNCLI10" for linked server "LinkedServerName" 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 "SQLNCLI10" for linked server "LinkedServerName". The provider supports the interface, but returns a failure code when it is used.
Then most probably you are trying to link a new SQL x64 version with an old SQL 2000 x32 Server. The error happens as when the new SQL Server x64 version is trying to run the distributed queriy on the linked server it uses the stored procedure which is called “sp_tables_info_rowset_64” which is definitely not on the old x32 versions but if you do the other way around linking the old to new then it works as it calls the “sp_tables_info_rowset” which is already on the new x64 versions.
There are two workarounds
1. The easy way which resolves the problem immediately and only that problem.
To fix the issue just add the stored procedure “sp_tables_info_rowset_64” on the old SQL Server Database and things should work fine.
To add the the stored procedure just run the following on the Query Analyzer of the old SQL Server
CREATE PROCEDURE sp_tables_info_rowset_64 @table_name SYSNAME, @table_schema SYSNAME = NULL, @table_type SYSNAME(255) = NULL AS DECLARE @Result INT SET @Result = 0 EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type GO
2. Now if you want to do the proper way you have to manually run the script called instcat.sql which you can find at the “<SQL Server Installation Path>MSSQLInstall”.
But before doing that you need to make sure that you have at least SP3 on your SQL Server 2000 or SQL Server 7.0
Now to run “instcat.sql” manually you need copy and paste this on your Query Analyzer on your Old SQL Server Database
osql -E -S <LinkedServerName> -i <Location>instcat.sql