Excellent article from Linchi Shea on performance issues using linked servers.
To summarise his article:
For the local optimiser to have access to remote statistics the connection to linked server must be at least one of: table owner, sysadmin, db_owner or db_ddladmin. Without these statistics query optimiser may well decide to scan the remote table and copy data to local for evaluation.
May 2010: I have recently been investigating linked server queries from 2008 to some versions of 2000, it is apparent from this investigation that queries to some versions of 2000 do not work. Worth adding that 2008 was 64bit and all versions of 2000 were 32 bit.
For example, running this query from 2008 to 2000 SP4 and 2000 SP4+patches:
select count(*) from db1.dbo.tab006
From 2008 to 2000 SP4 + patches -
OLE DB provider "SQLNCLI10" for linked server "db1" returned message "Unspecified error". OLE DB provider "SQLNCLI10" for linked server "db1" 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 "db1". The provider supports the interface, but returns a failure code when it is used.
From 2008 to 2000 SP4 -
(No column name) 998616
MS KB article 906954 refers -
“This problem occurs because the system stored procedures were not upgraded. You must manually upgrade the system stored procedures after you install SQL Server 2000 SP3 or SQL Server 2000 SP4.”
That sounds like the answer but I've read the SP4 install guide and it doesn't mention manually upgrading stored proc's (unless I missed it). There are however, numerous articles e.g. http://weblogs.asp.net/uber1024/archive/2007/07/07/linking-64-bit-sql2k5-to-32-bit-sql2k-or-how-to-avoid-wasting-10-days-waiting-for-straight-answers.aspx
suggesting that all we need to do is create a new SP (on db1) as follows:
create procedure sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = null as declare @Result int select @Result = 0 exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
Given the error message, this sounds reasonable, I have yet to try it.