none
Linked Server error - Msg 7311, Level 16, State 2

    Question

  • We have three SQL Servers:

    1. SQL 2008 Instance w/ SP1 cumulative update #5; running on Windows Server 2008 r2
    2. SQL 2008 Instance w/ SP1 cumulative update #5; running on Windows Server 2003 r2
    3. SQL 2000 Instance w/ SP4; running on Windows Server 2003 r2 W/ SP2

    We have a linked server connection from #2 to #3, which works perfectly.  However, our linked server connection from #1 to #3 does not work.  here is the error message we get:

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

    I've read articles saying we may need to run a specific script on the server we are linking to, due to missing SPs, but how can that be possible if the other linked server connection works?  We are linking from two identical SQL Server instances (other than the OS)...

    The only other difference I know of, is that #1 was an upgrade from SQL 2005, but #2 was a clean install of SQL 2008.

    Suggestions? 


    Clint
    • Edited by NashVegas Thursday, August 12, 2010 3:20 PM forgot something
    • Moved by KJian_ Friday, August 13, 2010 5:27 AM (From:SQL Server Data Access)
    Thursday, August 12, 2010 3:19 PM

All replies

  • Hi NashVegas

    Use "microsoft OLE DB Provider for SQL Server" provider instead of "SQL Native Client 10". Because your are connecting to SQL 2000 version. SQL 2000 wont support "SQL Native clinet 10".


    Ramesh S
    www.sequalserver.com
    Friday, August 13, 2010 7:53 AM
  • Hi NashVegas,

    Based on my research, the issue should be caused by the same you mentioned that stored procedure is missing or is not upgraded.

    According to Knowledge Base http://support.microsoft.com/default.aspx?scid=kb;EN-US;906954, the issue may happen while connecting from 64-bit SQL Server 2005 or SQL Server 2008 to 32-bit SQL Server 2000 or SQL Server 7.0.
    So, I would like to ask your help to check if SQL Server 2 is 32-bit version and SQL Server 1 is 64-bit version in this case? If so, the reason why we can connect from server 2 to server 3, but not from server 1 to server 3 is that this is a 64-bit SQL Server issue.

    Also, could you please help to check if we are able to connect from server 2 to server 3 currently?

    Anyway, could you please follow the KB I posted before to check if the linked servers work?

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Monday, August 16, 2010 9:29 AM
    Moderator
  • I really don't think this is the case otherwise in my .Net applications I'd have to determine the version of the target SQL server, then instantiate the appropriate connection object.  Things just don't work that way.   I'm pretty sure you will find that a SQL Native Client 10 provider can connect to anything from SQL 2008 and below, but not anything above (once it is released :).

    The real issue is given in the message. Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO"

    If you source server is 64 bit and your target is 32 bit then you will need to do the following.  (I have to pass credit to Kun Sik Lee of Costar Group for this one)

     

    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

    use [master]
    GO

    GRANT EXECUTE ON [dbo].[sp_tables_info_rowset_64] TO [LinkServerUser]
    GO


    • Proposed as answer by Darren Myher Wednesday, February 15, 2012 6:03 PM
    Monday, August 16, 2010 5:16 PM
  • EdHellyer's answer is the solution if you are going from 64bit to 32bit with Linked Server and already have the procs from the instcat.sql script installed on the master db.  Adding this sp_tables_info_rowset_64 stored procedure (which essentially passes you through to the 32bit version) fixes the issue.
    • Edited by MarkDBA Thursday, September 11, 2014 9:49 PM
    Thursday, September 11, 2014 9:45 PM