none
Sql Server Management Studio not recognising my instance.

    Question

  • Hi,

    I am running Windows 7 64 bit.

    I have installed MS OFFICE 2010 Outlook BCM which has created a SQL Server 2008 Express instance called MSSMLBIZ on my system.

    I have installed Visual Studio Professional 2010 which installes a Sql Server 2008 Express instance called SQLEXPRESS on my system.

    I then attempted to install Sql Server Management Studio Express 2008 but this failed (lack of SP1 I think) so I installed the R2 version of the same which went in fine ( only a warning about Windows Firewall being up and needing to check what ports were open if I needed remote access). I did NOT use this to upgrade either of the two instances from 2008 to 2008 R2.

    When I've run SSMSE 2008 R2, the "Connect to Server" dialog box listed only the MSSMLBIZ instance. The "Browse for more..." link listed only the MSSMLBIZ.

    When I run Sqlcmd -L in a Command window, NO servers are listed.

    I manually typed SQLEXPRESS into the "Connect... dialgoue and it did seem to open that instance.

    It seems to access the SQLEXPRESS instance normally but I am concerned that, if the instance wasn't listed automatically and, if Sqlcmd -L doesn't show either instance, I have problems which may escalate in the futeure.

    Can anyone show me how I can check my two instances are properly installed and that SSMSE 2008 R2 is linking to them properly?

    n.b. If I choose MSSMLBIZ to connect to, I get an error:

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

    Cannot connect to <ServerName>\MSSMLBIZ.

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

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

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

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

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

    Any guidance would be appreciated. Thanks.

    Thursday, August 05, 2010 6:18 AM

Answers

  • Hi Bankside,

    Thanks for your post here!

    In your description, you mentioned that the SQL Server Management Studio (SSMS) does not list all local servers in the Local Servers tab in the Browse for Servers dialog. However, these local servers can be found in the Network Servers. If I have misunderstood you, please feel free to let me know.

    The Local Servers tab in the Browse for Servers dialog, directly read registry entries in the System Registry. By default, the local instance info locates at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names. When you install an instance of SQL Server, appropriate entries will be created under this entry.

    The Network Servers tab in the Browser for Servers, send an UDP package to SQL Server Browser Services on the network servers, and then SQL Server Browser will get a list of installed instances, send back and all the instances shown in the Network Servers tab.

    Now, the problem why you cannot see the local servers under the Local Server tab, the reason may due to SQL Server cannot get the appropriate registry entry information in the System Registry. To work around this issue, you can manually input the server name which will be show in the server name drop-down list, or select the local server from Network Servers (ensure that the SQL Server Browse Services is running).

    For the second issue about connection problems please ensure that the SQL Server Services is running and use the appropriate SQL protocols, specify the correct server name and appropriate credential. You can confiure and check this in the SQL Server Configuration Manager. Besides, before connecting to SQL Server, you can create a UDL file to test the connection (create a plain text file and rename the file extension from txt to udl).

    If you have more queries, please let me know.

    Thanks,
    Chunsong


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, August 09, 2010 7:36 AM
    Moderator

All replies

  • 1) SQL Server Browser service is running?

    2) TCP port is enabled?

    3) Right click on SSMS and then Run As Administrator


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, August 05, 2010 6:49 AM
    Answerer
  • If your Sqlcmd -L dont catch it and you can connect localy i would say its because you Browser service is not running and then it is expected behavior.

     

    - Raoul

    Thursday, August 05, 2010 7:29 AM
  • Open SQL Server Configuration Manager from the Start menu (Programs --> MS SQL Server --> Configuration Tools-->SQL Server Configuration Manager.)

    Click on Services on the left side panel. On the right side of the panel, check for the service named with MSSQLSERVER. If you have already installed named instances, then the service will be like this: MSSQLSERVER\SQLExpress.

    Make sure the MSSQLServer service is started.

    Click on on SQL Server Network Configuration --> Expand it. Click on the protocol for the specific instance, like (Protocols for MSSQLSERVER\SQLExpress). Check the protocol that are enabled, by default, TCP/IP is disabled for the Express edition. If you want to connect using TCP/IP, enable it, and configure the IP Address and port number for the specific instance. Make sure none of the other instances are using the same IPAddress & port number.

    Once you make the necesary changes, you need to restart your MSSQLSERVER services, to make the changes permanent.

    Then try to connect using the IP, I think you will be able to connect.

    Hope, this may help.


    SKG: Please Marked as Answered, if it resolves your issue.
    Thursday, August 05, 2010 7:43 AM
  • Yes, the browser service is running. I don't need TCP access: all this is local. Running as administrator doesn't help.

    I did, however, find the instance I installed in the Network section of the dialogue box. Why is that? I've installed it (SQLServer 2008 via Visual Studio) locally and access to it via the SSMSE is from the same computer.

    Thursday, August 05, 2010 12:12 PM
  • The browser was running and I don't need TCP access, all is local.

    I have foind the Visual Studio instance by clicking on the network section of the connection dialogue. Why is that? The database and SSMSE are local.

    Thursday, August 05, 2010 12:13 PM
  • I believe i have seen some funny stuff regarding registry entries from when SQL Server express gets installed as part of Visual Studio compared to stand alone.

    It wouldn't surprise me if this is the reason you cant see it as a "normal" sql server installation, as it is a backbone development database for visual studio.

     

    - Raoul 

    Friday, August 06, 2010 9:49 AM
  • Have you tried accessing by using  <machinename>\SQLEXPRESS?

    And have you checked in Services (Control Panel> Administrative Tools) for the SQLExpress Instance under MSSQLSERVER Services


    Enrique Lima Solutions Architect | Apparatus, Inc. | http://geekswithblogs.com/enriquelima
    Friday, August 06, 2010 9:09 PM
  • Hi Bankside,

    Thanks for your post here!

    In your description, you mentioned that the SQL Server Management Studio (SSMS) does not list all local servers in the Local Servers tab in the Browse for Servers dialog. However, these local servers can be found in the Network Servers. If I have misunderstood you, please feel free to let me know.

    The Local Servers tab in the Browse for Servers dialog, directly read registry entries in the System Registry. By default, the local instance info locates at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names. When you install an instance of SQL Server, appropriate entries will be created under this entry.

    The Network Servers tab in the Browser for Servers, send an UDP package to SQL Server Browser Services on the network servers, and then SQL Server Browser will get a list of installed instances, send back and all the instances shown in the Network Servers tab.

    Now, the problem why you cannot see the local servers under the Local Server tab, the reason may due to SQL Server cannot get the appropriate registry entry information in the System Registry. To work around this issue, you can manually input the server name which will be show in the server name drop-down list, or select the local server from Network Servers (ensure that the SQL Server Browse Services is running).

    For the second issue about connection problems please ensure that the SQL Server Services is running and use the appropriate SQL protocols, specify the correct server name and appropriate credential. You can confiure and check this in the SQL Server Configuration Manager. Besides, before connecting to SQL Server, you can create a UDL file to test the connection (create a plain text file and rename the file extension from txt to udl).

    If you have more queries, please let me know.

    Thanks,
    Chunsong


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, August 09, 2010 7:36 AM
    Moderator
  • Hi

    I have also got above problem but I can logged in with this instance.

    I have another problem.

    When i am synchronise my local database with server database, First time synchronise dialog is involked and this dialog is filled up with local database instance name (ie. XYZ\SQLEXPRESS) and database name(ie.TESTDATA).

    But local database is not found so that program is crashed during filled up the first time synchronise dialog.

    So can you please suggest me how can I browse local database in local server dialog in SSMS?

    I have got local database in network server but i want to browse in local server only.

    Its appriciate if anyone solve my problem.

    Thanks,

    Jignesh



    • Edited by JMithapara Thursday, October 25, 2012 7:22 AM
    Thursday, October 25, 2012 6:38 AM