none
SQL Management Studio - "Server name" duplicity

    Question

  • hHi, where (or how) can I edit items on "Server name" list?

    when I start SQL SMS, there is "Connect to Server" window - in it, there is "Server name:" field. problem is, that I have same values twice. "WDOLKON\SQLEXPRESS" which is instance of local SQL Express server. connection settings is identical, but it is listed twice for some reason - item was created after second connection to this server maybe (?).
    I was looking for any configuration file, and I have found a .xml on Application Data, but it did not contain much conrete values. so I think, it is "hidden" in binary file on same directory.

    is there any way, how to remove one of these records?
    Tuesday, January 06, 2009 6:45 PM

Answers

  •  Hi

    User settings like recently used server list are stored in %APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin. If you rename this file and relaunch ssms, you would not see the MRU server list

    Thanks
    Sreekar

    • Marked as answer by wdolek Wednesday, February 04, 2009 9:09 AM
    Thursday, January 08, 2009 10:47 AM

All replies

  •  Hi

    AFAIK Removing a particular record is not possible, but you can remove all the connections by doing this.
    You can blow away all your Most Recently Used lists (including MRU connections) by deleting c:\Documents and Settings\<you>\Application Data\Microsoft SQL Server\90\Tools\Shell\mru.dat while Management Studio is not running. Management Studio will recreate the file the next time it starts.

    Thanks
    Sreekar

    Wednesday, January 07, 2009 4:59 AM
  • i'm using SQL Server Management Studio 10.0.1600.22 (for SQL server 2008). there is no "MRU" file :((
    Thursday, January 08, 2009 10:15 AM
  •  Hi

    User settings like recently used server list are stored in %APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin. If you rename this file and relaunch ssms, you would not see the MRU server list

    Thanks
    Sreekar

    • Marked as answer by wdolek Wednesday, February 04, 2009 9:09 AM
    Thursday, January 08, 2009 10:47 AM
  • :) thanks ;)

    Wednesday, February 04, 2009 9:08 AM
  • Looks like this file is a binary serialized version of the Microsoft.SqlServer.Management.UserSettings.SqlStudio class defined in the Microsoft.SqlServer.Management.UserSettings, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 assembly (located at c:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.Management.UserSettings.dll).

     

    With a bit of development skill (Visual Studio or even Powershell) you can deserialize this file into the original class, find the entries you want to remove and re-serialize the file back out.

     

    This should give you the idea (working on a copy of the .bin file)...

     

      var binaryFormatter = new BinaryFormatter();
    
      var inStream = new MemoryStream(File.ReadAllBytes(@"c:\temp\SqlStudio.bin"));
    
      var settings = (SqlStudio) binaryFormatter.Deserialize(inStream);
    
      foreach (var pair in settings.SSMS.ConnectionOptions.ServerTypes)
    
      {
    
            ServerTypeItem serverTypeItem = pair.Value;
    
            List<ServerConnectionItem> toRemove = new List<ServerConnectionItem>();
    
            foreach (ServerConnectionItem server in serverTypeItem.Servers)
    
            {
    
                    if (server.Instance != "the server you want to remove")
    
                    {
    
                            continue;
    
                    }
    
                    toRemove.Add(server);
    
            }
    
            foreach (ServerConnectionItem serverConnectionItem in toRemove)
    
            {
    
                    serverTypeItem.Servers.RemoveItem(serverConnectionItem);
    
            }
    
      }
    
      
    
      MemoryStream outStream = new MemoryStream();
    
      binaryFormatter.Serialize(outStream, settings);
    
      byte[] outBytes = new byte[outStream.Length];
    
      outStream.Position = 0;
    
      outStream.Read(outBytes, 0, outBytes.Length);
    
      File.WriteAllBytes(@"c:\temp\SqlStudio.bin", outBytes);
    

     

    Thursday, June 16, 2011 3:49 PM
  • that almost works, except that it is not marked as serializable in that dll.


    Gray Knight

     

    I found the answer here, that takes what is above, but added the reflection part to allow re-serialization.

     

    http://stackoverflow.com/questions/6230159/how-to-delete-server-entries-in-sql-server-management-studios-connect-to-server

     

     

    • Edited by DarkGray Knight Thursday, October 06, 2011 10:09 PM found answer
    Thursday, October 06, 2011 6:40 PM