We are hiring!

Xtivia, Inc.
2012 North America Liferay Partner of the Year

Join our top-notch team of Enterprise Java and Liferay Portal professionals in our Austin, TX office or work remotely! Please check out our current Dice listings.

Recent Bloggers

Bob Dietrich

Posts: 1

Stars: 0

Date: 6/14/13

KL Hamilton

Posts: 3

Stars: 0

Date: 6/6/13

Luke Smith

Posts: 9

Stars: 0

Date: 5/24/13

Brian Fairchild

Posts: 19

Stars: 0

Date: 5/16/13

Mangesh Mharolkar

Posts: 10

Stars: 0

Date: 5/7/13

Barrie Shaw

Posts: 4

Stars: 0

Date: 4/17/13

Andrew Dalby

Posts: 2

Stars: 0

Date: 4/5/13

Tina Xing

Posts: 1

Stars: 0

Date: 3/29/13

Kaushik Nagaraj

Posts: 7

Stars: 0

Date: 3/27/13

Asier Del Pozo

Posts: 3

Stars: 0

Date: 3/27/13

« Back

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider

8/6/12 5:38 PM

While creating a linked server from SQL Server 2005 to SQL Server 2000, I 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.

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 system databases. In case something went wrong with the SQL Server installation then the problem could get worse.

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

Instead I found a small workaround that seems to have been successfully implemented by a lot of folks. This is not a solution that I came up with but saved me a lot of time so wanted to share it with the community.

Just create the following Stored Procedure on the SQL Server 2000 instance, in the MASTER database and GRANT EXECUTE to the Public.

USE master

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

As noted earlier do not forget to GRANT EXECUTE on this SP to the public or whoever needs to use the linked server.

USE master

GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

Hope this helps........

By Kaushik Nagaraj

5518 Views, 15 Comments

Blog Topics: Databases sql server 2000 linked server workaround

Comments

Trackback URL:

Add Comment

Anonymous User

Nice looking out Kaushik,

This worked well for me.

Posted on 10/18/12 11:57 AM.

Anonymous User

Wow, this worked perfect for me too! Thank You very much for saving me search time!!!

Posted on 10/26/12 1:41 PM.

Anonymous User

Thanks a million Kaushik!! I was having this exact problem and solved it with your suggestion

Posted on 11/23/12 6:09 AM in reply to Anonymous User.

Anonymous User

Thanks, It works for me, PUBLIC access can create security risk, so good if we use any user group.

Posted on 11/29/12 4:33 PM.

Anonymous User

Great job !

Posted on 12/14/12 3:24 AM in reply to Anonymous User.

Anonymous User

I used this successfully on SQL Server 2000 after creating a Linked Server on SQL Server 2008 R2

Posted on 12/27/12 12:51 PM in reply to Anonymous User.

Anonymous User

(MS DTC) has stopped this transaction i have got this error

Posted on 2/9/13 5:12 AM in reply to Anonymous User.

Kaushik Nagaraj

I haven't seen that error before so I am not sure what do here.

Posted on 2/11/13 5:29 PM in reply to Anonymous User.

Anonymous User

Very nice ! Worked flawless without installing the hotfix !
Connection was needed between SQL Server 2000 Sp4 - 2008R2

Posted on 2/18/13 6:08 AM.

Anonymous User

This solution doesn't work for me. Am I missing something ?

Posted on 2/20/13 2:23 AM.

Anonymous User

Can you post the error if you get any? Did you create it under the master DB?

Posted on 2/20/13 9:27 AM in reply to Anonymous User.

Anonymous User

Great, works... Thank you

Posted on 3/8/13 7:41 AM.

Anonymous User

this falls in the category of FN Magic.

Posted on 4/5/13 2:04 PM in reply to Anonymous User.

Anonymous User

Don't usually post replies to forums as I don't find such fantastic help as this, superb, thanks a million

Posted on 4/11/13 11:19 AM in reply to Anonymous User.

Dan Cote

I had to do a little twist to your suggestion because I was getting an error "Cannot find data type SYSNAME". (Is it because I do not have the sa user?). Anyhow, I replaced SYSNAM with VARCHAR(128) and it worked like a charm!!
Many thanks Kaushik!

Dan Cote.

USE master
GO

CREATE PROCEDURE sp_tables_info_rowset_64

@table_name VARCHAR(128),
@table_schema VARCHAR(128) = 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

USE master

GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

Posted on 5/13/13 3:15 PM.