一尘不染

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

c#

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

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

我已经在Internet上进行了大量研究,并确保(1)Sql Browser正在运行,以及(2)UDP端口1434已打开。

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


阅读 478

收藏
2020-05-19

共1个答案

一尘不染

非常感谢Mitch提出的出色答案。但是,我最终完成的工作如下:

我有两种单独的方法分别获取本地和远程服务器实例。从注册表中检索本地实例。您需要同时搜索WOW64和WOW3264配置单元,才能同时获得SQL Server
2008(64位)和SQL Server Express(32位)

这是我使用的代码:

/// <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,
                                                                             @"SOFTWARE\Microsoft\Microsoft SQL Server",
                                                                             "InstalledInstances");

     string[] instances32Bit = registryValueDataReader.ReadRegistryValueData(RegistryHive.Wow6432,
                                                                             Registry.LocalMachine,
                                                                             @"SOFTWARE\Microsoft\Microsoft 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] == '\0')
           {
              j = 0;
              string str = Encoding.Default.GetString(temp).Trim('\0');

              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.

最后,我只是合并了远程实例列表和本地实例列表以产生最终结果。

2020-05-19