SQL 2005 and Linked server (Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO”) | Raffaeu’s blog

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
    @table_name sysname,
    @table_schema     sysname = null, 
    @table_type nvarchar(255) = null
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.

Leave a comment

Your email address will not be published. Required fields are marked *