如何在 SQL Server Management Studio 的“连接到服务器"中删除服务器条目屏幕? [英] How to delete server entries in SQL Server Management Studio's "Connect to Server" Screen?

问题描述

可能的重复:
如何删除服务器"姓名&rdqu;SQL Server Management Studio 历史记录中的项目

在连接到服务器"屏幕中，SQL Server Management Studio 存储您曾经为服务器名称、登录名和密码输入的所有条目.这很有帮助，但有时情况会发生变化，服务器地址会发生变化，数据库不再可用.

In the "Connect to Server" screen, SQL Server Management Studio stores all entries you have ever entered for Server Name, login and password. This is very helpful, but from time to time things change, servers addresses change, databases are no longer available.

如何从此屏幕删除服务器条目?此外，当您选择服务器时，列表中会显示过去的登录信息.再一次，这些变化.如何删除用户条目?

How can I delete server entries from this screen? Also, when you select a server, past logins are available in the list. Once again, these change. How can I delete user entries?

推荐答案

看起来这个文件是 Microsoft.SqlServer.Management.UserSettings.SqlStudio 类的二进制序列化版本Microsoft.SqlServer.Management.UserSettings, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 程序集(位于 c:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.Management.UserSettings.dll).

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).

借助一些开发技能(Visual Studio 甚至 Powershell)，您可以将此文件反序列化为原始类，找到要删除的条目并将文件重新序列化.

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.

这应该给你一个想法(处理 .bin 文件的副本)...

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);

在 Adrian 提出问题之后，我在使用 Visual Studio 2010 的 Win7 x64 机器上再次尝试了这个.我发现了同样的错误，因此，在挖掘了一下之后，我发现它需要一些步骤来解决.

After Adrian's question, I tried this again on a Win7 x64 box using Visual Studio 2010. I found the same error so, after digging a bit I found it took a number of steps to resolve.

1. 在项目属性中将平台目标设置为x86"
2. 添加对 Microsoft.SqlServer.Management.SDK.SqlStudio 的引用(在我的机器上，它位于 c:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.Management.Sdk.SqlStudio.dll)
3. 添加对 Microsoft.SqlServer.Management.UserSettings 的引用(在上一个的同一目录中)
4. 执行自定义程序集解析

自定义程序集解析需要做一些事情，因为这并不明显(至少对我而言)为什么 CLR 不能正确解析程序集以及为什么 Visual Studio 不允许我手动添加引用.我说的是 SqlWorkbench.Interfaces.dll.

The custom assembly resolution took a bit of doing since it wasn't obvious (to me, at least) why the CLR wouldn't just resolve the assembly correctly and why Visual Studio wouldn't allow me to add the reference manually. I'm talking about the SqlWorkbench.Interfaces.dll.

更新后的代码如下:

internal class Program
    {
        static void Main(string[] args)
        {
            AppDomain.CurrentDomain.AssemblyResolve += CurrentDomain_AssemblyResolve;

            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);
        }

        private static Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
        {
            Debug.WriteLine(args.Name);
            if (args.Name.StartsWith("SqlWorkbench.Interfaces"))
            {
                return Assembly.LoadFrom(@"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbench.Interfaces.dll");
            }
            return Assembly.Load(args.Name);
        }
    }

这篇关于如何在 SQL Server Management Studio 的“连接到服务器"中删除服务器条目屏幕?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！