If you have a SQL 2005 machine with a (x64) installation, some of the system queries have a different name, like [some_command]sys_64. So when you try to connect your powerfull SQL2005 to an old remote SQL2000, probably in x32 version, you can receive a strange error like Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO”.
In many forums you can find a link to a microsoft KB that explains to you that you have to install the SP4 in the old SQL and maybe everything will done.
Usually I like to know why something doesn’t run …
So, when you execute from SQL2005 a query like
select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N’mytable’, N’dbo’, NULL
So you can also try to add this stored in the Remote SQL2000 server, in the master database:
create procedure sp_tables_info_rowset_64
declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
It works and you don’t need to run strange Package on your critical machine.