Friday, February 10, 2012

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "XXXXXX"

I wanted to create a linked server from SQL Server 2005 to SQL Server 2000 and got the following error when I tried to query the remote server.

OLE DB provider "SQLNCLI10" for linked server "XXXXXX" 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 "XXXXXX". The provider supports the interface, but returns a failure code when it is used.

So after some initial research found that there is a Microsoft support page for this error but requires SP3 or higher on the 2000 instance and also requires us to run a particular SQL file called 'instcat.sql in the INSTALL directory for MSSQL. But such a change would require backups of the master database. In case something went wrong.

Ref : http://connect.microsoft.com/SQLServer/feedback/details/465959/unable-to-query-linked-sql-server-2000

So I found this small workaround with less hassles.
Just create the following Stored Procedure and GRANT EXECUTE to the Public.

CREATE PROCEDURE sp_tables_info_rowset_64

@table_name SYSNAME,
@table_schema SYSNAME = NULL,
@table_type nvarchar(255) = NULL
AS

DECLARE @Result INT SET @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
GO


Hope this helps........

23 comments:

  1. THANK YOU FOR THE INFORMATION, SO GREAT WORK, FUNCIONO GENIAL, CON UN ERROR MUY SIMILAR,

    ReplyDelete
  2. Hello,

    Looks like very interesting, on wich server / database should I create / execute this SP ?

    Thanks !

    ReplyDelete
    Replies
    1. On second thoughts, create this procedure on SQL 2000 master database

      Delete
    2. Don't forget to grant execute on this SP to the user.

      GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

      Delete
  3. Valeu mano, exatamente o que eu precisava!

    ReplyDelete
  4. thank you so much

    ReplyDelete
  5. Thanks that works perfectly!

    ReplyDelete
  6. Hi there,
    Thanks so much for your sharing first, however, I found NO sp_tables_info_rowset system stored procedure on SQL Server 2000 machine which will cause sp_tables_info_rowset_64 to fail. Any idea?

    ReplyDelete
  7. Did you look in the master database? I forgot to put a USE DB in the TSQL.

    ReplyDelete
  8. Wonderful! Thank you for posting this...

    ReplyDelete
  9. Thanks! Save me a lot of time.

    ReplyDelete
  10. nice workaround and works like a charm .. thanks a lot for sharing

    ReplyDelete
  11. Thanks alot, u saved my life

    ReplyDelete
  12. This Worked Perfect....we have been struggling with this for Dayzzzzzzzz!!!

    ReplyDelete
  13. Thank you very much legend worked perfect

    ReplyDelete
  14. Thanks a lot! Mucho bueno! ;)

    ReplyDelete
  15. HOla amigo que es lo que hace esté procedimiento, no lo logro comprender del todo, mi pregunta va porque lo he usado y funciona perfectamente, pero para poderlo utilizaar me han pedido que justifique porque es necesario usarlo tienes alguna idea ?? gracias de antemano un cordial saludo.

    ReplyDelete
    Replies
    1. I don't know the reason either. This was given on a Microsoft support page. So the question should go to Microsoft.
      http://connect.microsoft.com/SQLServer/feedback/details/465959/unable-to-query-linked-sql-server-2000

      Delete