locked
Linked server error - invalid schema or catalog was specified for the provider "SQLNCLI11" for linked server... RRS feed

  • Question

  • I am getting this error; An invalid schema or catalog was specified for the provider "SQLNCLI11" for linked server "; in sql profiler just after this stored procedure is called:  "exec [DBName].[sys].sp_tables_info_90_rowset_64 N'tblXYZ',NULL,NULL" 

    The SQL Server is 

    Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) - 12.0.5556.0 (X64) 
    Aug 17 2017 12:07:38 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    I have been able to successfully query the table referenced by the "rowset" and return the data in SSMS against the database. I can query from the linked server successfully as well. 

    I have 2 servers with identical setup and the only thing I know that has changed is this "server" having the issue was renamed from x to y. 

    The other server that works was not renamed. 

    The sp_dropserver/addserver commands were used to change the name in sql server and after reboot the: Select * from sys.server and the Select @@server names match but still getting the same error. 

    I also tried dropping the linked server and re-adding it and I still get the same error. 

    Is there something else I can check? Any help will be appreciated. 


    Sue


    • Edited by OutThere Friday, October 12, 2018 1:42 PM
    Wednesday, October 10, 2018 9:32 PM

Answers

  • Hi OutThere,

     

    Thank you for your reply.

     

    As you can see  that 'An invalid schema or catalog was specified for the provider "SQLNCLI11" for linked server ', may be you can check your name in your script.You can use the four part naming convention when using linked servers.However, you can assign an ALIAS so that the four part name is only used in the FROM clause.Linked servers DO NOT have default values for the four part names. Default values only apply to the current server/database connection.

     

    There is an article which has similar issue , you can refer to it : https://sqlcurve.wordpress.com/2013/05/20/an-invalid-schema-or-catalog-was-specified-for-the-provider-sqlncli10-for-linked-server/

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by OutThere Wednesday, October 17, 2018 3:05 AM
    Thursday, October 11, 2018 10:57 AM

All replies

  • Hi OutThere,

     

    Thank you for your reply.

     

    As you can see  that 'An invalid schema or catalog was specified for the provider "SQLNCLI11" for linked server ', may be you can check your name in your script.You can use the four part naming convention when using linked servers.However, you can assign an ALIAS so that the four part name is only used in the FROM clause.Linked servers DO NOT have default values for the four part names. Default values only apply to the current server/database connection.

     

    There is an article which has similar issue , you can refer to it : https://sqlcurve.wordpress.com/2013/05/20/an-invalid-schema-or-catalog-was-specified-for-the-provider-sqlncli10-for-linked-server/

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by OutThere Wednesday, October 17, 2018 3:05 AM
    Thursday, October 11, 2018 10:57 AM
  • Hi Rachel,

    I don't think that there is a space in the DBName because I can grab the sql out of profiler that is throwing the error and put it in the SSMS query window and run it successfully. 

    This server had a name change after the application was installed and someone ran the drop/add server to change the name in sys.server to match the server name. 

    It is after that the linked server started getting the error when a process ran.

    I read the msdn article about the drop/add server and it said it can affect linked servers but I tried dropping the linked server and recreating it under the administrator account and I am still getting the error. 

    Any ideas on how the "drop/add server" affects linked servers? 


    Sue

    Friday, October 12, 2018 1:41 PM
  • I have some difficultites to understand this. Maybe you could make some clarifications. The statement you have posted, that executes on the remote server, doesn't it? What is the statement on the local server?

    You appear to have been running Profiler against the remote server, or am I misunderstanding? Because you also seem to say that you get this error message on the remote server. But that looks some thing that would pop up on the local server. Maybe you can post a screenshot of the trace?

    Please also post the statement and the error message on the local server.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, October 12, 2018 9:12 PM
  • Hi Erland,

    I figured the issue out this weekend. The issue was a stored procedure had a variable that was 36 characters long but the linked server name was 39 characters so it was truncating the "dbo" off of the name and sql was returning an invalid schema. 

    I want thank everyone for replying to my post and I am marking Rachel's reply because it was the "name" but not because of spaces and giving you points too. 

    Thank you,

    Sue


    Sue

    Wednesday, October 17, 2018 3:05 AM