none
Messed up SQL Server by removing older versions

    Question

  • I installed SQL Server 2016 Developers edition on my laptop.  I already had SQL Server Developer edition 2008, 2012, and 2014 installed.  Because of limited space on my hard drive I removed the older versions of SQL Server.  I think I removed too much because now I get this error when trying to log on to my local server in SSMS:

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to MYLAPTOPSERVER.

    ------------------------------
    ADDITIONAL INFORMATION:

    A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

    ------------------------------

    No process is on the other end of the pipe

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    I can connect to other database servers in SSMS.  I have run the "Repair" for SQL Server with no change in the problem.

    I have gone into the "Sql Server Configuration Manager" and tried changing the order of the Client Protocols with no luck. 

    Any idea how to fix this problem?

    Thanks,


    Fred



    • Edited by MESfred Tuesday, May 2, 2017 7:10 PM
    Tuesday, May 2, 2017 1:06 PM

Answers

  • Have you used that trace flag to by pass the upgrade?

    What is your goal here - cause this instance seems hosed?

    Do you want a working version of SQL 2016? Have you tried another install of it?

    • Marked as answer by MESfred Thursday, May 11, 2017 6:52 PM
    Thursday, May 11, 2017 2:10 PM

All replies

  • Have you verified that your SQL 2016 instance is started?

    Do you see it if you do a SQLCMD -L from a command prompt?

    Tuesday, May 2, 2017 2:24 PM
  • SQLCMD -L does list (local).  In "Sql Server Configuration Manager" my SQL 2016 instance "SQL Server (MYLAPTOPSERVER)" says it is "Running".

    Fred

    Tuesday, May 2, 2017 2:48 PM
  • Can you ensure that shared memory is enabled? Go to sql configuration manager - ensure you get the sql 2016 one, drill down on native client, client protocols and see if share memory is enabled? Do this for both bit architectures.
    Tuesday, May 2, 2017 2:52 PM
  • Yes, Shared Memory is Enabled for both architectures.

    Fred

    Tuesday, May 2, 2017 5:02 PM
  • When I run this I get this:

    C:\>sqlcmd -s local
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a
     connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to
    allow remote connections. For more information see SQL Server Books Online..


    Fred

    Tuesday, May 2, 2017 7:14 PM
  • When I run this I get this:

    C:\>sqlcmd -S MYLAPTOPSERVER
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection because an error was encountered during
    handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to
     accept new connections or a resource limitation (memory or maximum allowed connections) on the server..
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Shared Memory Provider: No process is on the other end of the pipe.
    .
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection.
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection due to prelogin failure.


    Fred

    Tuesday, May 2, 2017 7:19 PM
  • Hello,

    Could you try to repair the SQL Server instance you left installed?

    https://docs.microsoft.com/en-us/sql/database-engine/install-windows/repair-a-failed-sql-server-installation


    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, May 2, 2017 8:32 PM
    Moderator
  • I have tried to repair the instance twice.  It completed both time with no errors, but it did not fix the problem.  Are there shared files from SQL Server 2008R2 that are not reinstalled by the Repair (that are needed)?


    Fred

    Tuesday, May 2, 2017 9:04 PM
  • Can you try sqlcmd -S.
    Tuesday, May 2, 2017 10:09 PM
  • In a previous post I show the result of sqlcmd -S, that it failed.  I do get a slightly different error depending whether I try to connect with Local or MYLAPTOPSERVER.

    The only server I can not connect to is my local one.  I can connect to servers that are not on my laptop.

    According to "SQL Server Configuration Manager" and sqlcmd -L, my instance is running.  I just can't log into it.


    Fred

    Wednesday, May 3, 2017 1:20 PM
  • Also, I tried connecting to my local server from another computer on the network. It failed with the following error:

    TITLE: Connect to Server

    Cannot connect to MYLAPTOPSERVER. 

    ADDITIONAL INFORMATION: A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476 

    BUTTONS: OK


    Fred


    • Edited by MESfred Wednesday, May 3, 2017 2:19 PM
    Wednesday, May 3, 2017 2:10 PM
  • I checked the ERRORLOG and noticed this error reoccuring:

    2017-05-09 08:21:12.60 Logon       Error: 17809, Severity: 20, State: 3.
    2017-05-09 08:21:12.60 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]

    I cannot connect to the server to run the sp_configure command.  Any suggestions?

    Thanks,


    Fred

    Wednesday, May 10, 2017 2:42 PM
  • It looks like the server is in upgrade mode. You might want to try to bypass this with the trace flag 902

    If this is not the case use the DAC to connect to your SQL Server. In Management studio try ADMIN:YourServerName

    then use sp_configure to increase the number of connections allowed.

    sp_configure 'user connections',0

    reconfigure with override

    Wednesday, May 10, 2017 2:53 PM
  • I get this error when running the sp_configure command:

    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0]

    The configuration option 'user connections' does not exist, or it may be an advanced option.


    Fred

    Thursday, May 11, 2017 1:49 PM
  • try

    exec sp_configure 'show advanced options',1

    reconfigure with override

    Thursday, May 11, 2017 1:53 PM
  • That completed successfully.

    I tried logging in and it still does not work.

    What should I try next?


    Fred

    Thursday, May 11, 2017 1:58 PM
  • Have you used that trace flag to by pass the upgrade?

    What is your goal here - cause this instance seems hosed?

    Do you want a working version of SQL 2016? Have you tried another install of it?

    • Marked as answer by MESfred Thursday, May 11, 2017 6:52 PM
    Thursday, May 11, 2017 2:10 PM
  • Yes, I put the trace flag in the startup parameters and restarted the server.

    My goal was to have a working local instance of SQL Server on my laptop.

    I'm afraid of going through the all the work of reinstalling and it still not working.

    It looks like I am going to have to "bite the bullet" and hope it works.

    Any recommendations to make sure I completely remove everything SQL Server?  I also have Visual Studio 2017 install with Datatools for SQL Server.

    Thanks,


    Fred

    Thursday, May 11, 2017 2:18 PM