SqlDataSourceEnumerator.Instance.GetDataSources() 找不到本地 SQL Server 2008 实例

问题描述

我使用以下代码列出所有远程和本地 SQL Server 实例:

I use the following code to list all the remote and local SQL Server instances:

public static void LocateSqlInstances()
  {
     using( DataTable sqlSources = SqlDataSourceEnumerator.Instance.GetDataSources())
     {
        foreach(DataRow source in sqlSources.Rows )
        {
           string instanceName = source["InstanceName"].ToString();

           if (!string.IsNullOrEmpty(instanceName))
           {
              Console.WriteLine(" Server Name:{0}", source["ServerName"]);
              Console.WriteLine("   Instance Name:{0}", source["InstanceName"]);
              Console.WriteLine("   Version:{0}", source["Version"]);
              Console.WriteLine();
           }
        }
        Console.ReadKey();
     }
  }

在我的本地机器上运行代码.该代码可以找到并列出已安装的 SQL Server express 实例(版本 9.0.5000)，但无法列出其他 SQL Server 实例(版本 10.0.1600).

running the code on my local machine. The code can find and list a SQL server express instance (version 9.0.5000) installed but failed to list the other SQL server instance (version 10.0.1600).

我在 Internet 上做了很多研究，并确保 (1) Sql Browser 正在运行和 (2) UDP 端口 1434 已打开.

I've done a lot of research on the Internet and made sure that (1) the Sql Browser is running and (2) the UDP port 1434 is open.

有人知道为什么吗?谢谢.

Anybody knows why? Thanks.

推荐答案

非常感谢 Mitch 提供的出色答案.但是，我最终所做的如下:

Thanks a lot to Mitch for the great answer he puts together. However, what I've done eventually is like the following:

我有两种不同的方法来分别获取本地和远程服务器实例.从注册表中检索本地实例.需要同时搜索WOW64和WOW3264 hives才能得到SQL server 2008(64位)和SQL server Express(32位)

I have two separate methods to get local and remote server instance respectively. The local instances are retrieved from the registry. You need to search both WOW64 and WOW3264 hives to get both SQL server 2008 (64bit) and SQL server Express (32 bit)

这是我使用的代码:

/// <summary>
  ///  get local sql server instance names from registry, search both WOW64 and WOW3264 hives
  /// </summary>
  /// <returns>a list of local sql server instance names</returns>
  public static IList<string> GetLocalSqlServerInstanceNames()
  {
     RegistryValueDataReader registryValueDataReader = new RegistryValueDataReader();

     string[] instances64Bit = registryValueDataReader.ReadRegistryValueData(RegistryHive.Wow64,
                                                                             Registry.LocalMachine,
                                                                             @"SOFTWAREMicrosoftMicrosoft SQL Server",
                                                                             "InstalledInstances");

     string[] instances32Bit = registryValueDataReader.ReadRegistryValueData(RegistryHive.Wow6432,
                                                                             Registry.LocalMachine,
                                                                             @"SOFTWAREMicrosoftMicrosoft SQL Server",
                                                                             "InstalledInstances");

     FormatLocalSqlInstanceNames(ref instances64Bit);
     FormatLocalSqlInstanceNames(ref instances32Bit);

     IList<string> localInstanceNames = new List<string>(instances64Bit);

     localInstanceNames = localInstanceNames.Union(instances32Bit).ToList();

     return localInstanceNames;
  }

public enum RegistryHive
{
  Wow64,
  Wow6432
}

public class RegistryValueDataReader
{
  private static readonly int KEY_WOW64_32KEY = 0x200;
  private static readonly int KEY_WOW64_64KEY = 0x100;

  private static readonly UIntPtr HKEY_LOCAL_MACHINE = (UIntPtr)0x80000002;

  private static readonly int KEY_QUERY_VALUE = 0x1;

  [DllImport("advapi32.dll", CharSet = CharSet.Unicode, EntryPoint = "RegOpenKeyEx")]
  static extern int RegOpenKeyEx(
              UIntPtr hKey,
              string subKey,
              uint options,
              int sam,
              out IntPtr phkResult);


  [DllImport("advapi32.dll", SetLastError = true)]
  static extern int RegQueryValueEx(
              IntPtr hKey,
              string lpValueName,
              int lpReserved,
              out uint lpType,
              IntPtr lpData,
              ref uint lpcbData);

  private static int GetRegistryHiveKey(RegistryHive registryHive)
  {
     return registryHive == RegistryHive.Wow64 ? KEY_WOW64_64KEY : KEY_WOW64_32KEY;
  }

  private static UIntPtr GetRegistryKeyUIntPtr(RegistryKey registry)
  {
     if (registry == Registry.LocalMachine)
     {
        return HKEY_LOCAL_MACHINE;
     }

     return UIntPtr.Zero;
  }

  public string[] ReadRegistryValueData(RegistryHive registryHive, RegistryKey registryKey, string subKey, string valueName)
  {
     string[] instanceNames = new string[0];

     int key = GetRegistryHiveKey(registryHive);
     UIntPtr registryKeyUIntPtr = GetRegistryKeyUIntPtr(registryKey);

     IntPtr hResult;

     int res = RegOpenKeyEx(registryKeyUIntPtr, subKey, 0, KEY_QUERY_VALUE | key, out hResult);

     if (res == 0)
     {
        uint type;
        uint dataLen = 0;

        RegQueryValueEx(hResult, valueName, 0, out type, IntPtr.Zero, ref dataLen);

        byte[] databuff = new byte[dataLen];
        byte[] temp = new byte[dataLen];

        List<String> values = new List<string>();

        GCHandle handle = GCHandle.Alloc(databuff, GCHandleType.Pinned);
        try
        {
           RegQueryValueEx(hResult, valueName, 0, out type, handle.AddrOfPinnedObject(), ref dataLen);
        }
        finally
        {
           handle.Free();
        }

        int i = 0;
        int j = 0;

        while (i < databuff.Length)
        {
           if (databuff[i] == '')
           {
              j = 0;
              string str = Encoding.Default.GetString(temp).Trim('');

              if (!string.IsNullOrEmpty(str))
              {
                 values.Add(str);
              }

              temp = new byte[dataLen];
           }
           else
           {
              temp[j++] = databuff[i];
           }

           ++i;
        }

        instanceNames = new string[values.Count];
        values.CopyTo(instanceNames);
     }

     return instanceNames;
  }
}


SqlDataSourceEnumerator.Instance.GetDataSources() is used to get remote sql server instances. 

最后，我只是合并了远程实例列表和本地实例列表来产生最终结果.

At the end, I just merge the remote instance list and local instance list to produce the final result.

这篇关于SqlDataSourceEnumerator.Instance.GetDataSources() 找不到本地 SQL Server 2008 实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！