none
Connection fails with remote SQL Server, SQLState: '01000'

    Question

  • Hi

    When I run a query like below against one remote SQL Server then it works fine and returns the required data;

    SELECT * FROM [ODBC;Driver=SQL Server;SERVER=Server1.com;DATABASE=MyDatabase;UID=MyUser;PWD=MyPass].MyTable

    However when I run the same query against a second remote SQL Server it gives me below error;

    Connection failed:
    SQLState: '01000'
    SQL Server Error: 14
    [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Invalid Instance()).
    Connection failed:
    SQLState: '08001'
    SQL Server Error: 14
    [Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.

    I can otherwise access the second SQL Server fine. So it would seem there is something minor missing in the second server configuration that blocks running the query in this way. What is it I need to do on the problematic SQL Server to have me run the query fine?

    Many Thanks

    Regards


    • Edited by Y a h y a Tuesday, February 7, 2012 4:34 AM
    Tuesday, February 7, 2012 4:33 AM

Answers

  • "Invalid instance" seems to indicate you are trying use the default instance but you should specify a specific instance. For example not "Server=myMachine" but "Server=myMachine\SQL2008". If you use SSMS to browse for servers, it will show all servers and their instance names.

    -Tom. Microsoft Access MVP

    • Marked as answer by Y a h y a Tuesday, February 7, 2012 7:10 PM
    Tuesday, February 7, 2012 2:21 PM

All replies

  • "Invalid instance" seems to indicate you are trying use the default instance but you should specify a specific instance. For example not "Server=myMachine" but "Server=myMachine\SQL2008". If you use SSMS to browse for servers, it will show all servers and their instance names.

    -Tom. Microsoft Access MVP

    • Marked as answer by Y a h y a Tuesday, February 7, 2012 7:10 PM
    Tuesday, February 7, 2012 2:21 PM
  • Hi Tom

    Many thanks. When I try below it works. You are a star.

    SELECT * 
    FROM [ODBC;Driver=SQL Server;SERVER=Server1.com\sql2008,1433;DATABASE=MyDatabase;UID=MyUser;PWD=MyPass].MyTable

    Thanks

    Regards

    Tuesday, February 7, 2012 7:10 PM