首頁>Program>source

我使用以下代碼列出所有遠端和本地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 Broswer正在執行,並且2-UDP埠1434已打開。

有人知道為什麼吗? 谢谢。

最新回復
  • 5月前
    1 #

    您正在跳過未命名例項的服務器.修改您的代碼:

    public static void LocateSqlInstances()
      {
         using (DataTable sqlSources = SqlDataSourceEnumerator.Instance.GetDataSources())
         {
            foreach (DataRow source in sqlSources.Rows )
            {
               string servername;
               string instanceName = source["InstanceName"].ToString();
               if (!string.IsNullOrEmpty(instanceName))
               {
                  servername =  source["InstanceName"] + '\\' + source["ServerName"];
               }
               else
               {
                  servername =  source["ServerName"];
               }
               Console.WriteLine(" Server Name:{0}", servername );
               Console.WriteLine("     Version:{0}", source["Version"]);
               Console.WriteLine();
            }
            Console.ReadKey();
         }
      }
    

    請註意: SqlDataSourceEnumerator.Instance.GetDataSources() 有缺點:

      Subject to firewall rules (Blocked TCP/IP 1433 and UDP 1434)

      Doesn't find SQL Servers if the SQL Browser is off

      Doesn't find SQL Servers if they are hidden

      List contents not guaranteed to be repeatable (due to timeouts). In fact, a subsequent call is quite likely to give a different list depending on the network I/O, server performance, number of servers on the network and other time-dependent constraints

    几个訊息来源說您必须打2个電话给 SqlDataSourceEnumerator.Instance.GetDataSources() ...

    參考:

      SqlDataSourceEnumerator.Instance; not returning all instances

      EnumAvailableSqlServers or SqlDataSourceEnumerator - Incorrect list of available Databases

      Enumerating SQL Servers

      Programmatically Listing SQL Servers

    p

    非常感谢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.
    

    最後,我只合並了遠端例項列表和本地例項列表以产生最终結果。

  • 5月前
    2 #

    有些事情人们需要了解GetDataSources和SqlDataSourceEnumerator的方法.如果例項名稱為預設值-例項名稱將為空! [為什麼..我不知道,為什麼我也不能指定冗长,也不知道,但MS的那个人寫了它……arrrgh]

    ServerName:服務器的名稱。

    InstanceName:服務器例項的名稱. Blank if the server is running as the default instance.

    IsClustered指示服務器是否為集群的一部分。

    服務器的版本(對於SQL Server 2000是8.00.x,對於SQL Server 2005是9.00.x)。

    从這裏: https://msdn.microsoft.com/zh-CN/library/system.data.sql.sqldatasourceenumerator.getdatasources(v=vs.110).aspx

  • 5月前
    3 #

    var registryViewArray = new[] { RegistryView.Registry32, RegistryView.Registry64 };
    foreach (var registryView in registryViewArray)
    {
        using (var hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, registryView))
        using (var key = hklm.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server"))
        {
            var instances = (string[]) key?.GetValue("InstalledInstances");
            if (instances != null)
            {
                foreach (var element in instances)
                {
                    if (element == "MSSQLSERVER")
                        Console.WriteLine(System.Environment.MachineName);
                    else
                        Console.WriteLine(System.Environment.MachineName + @"\" + element);
                }
            }
        }
    }
    Console.ReadKey();
    

  • matlab:OCR的字元重建和填充
  • c#:ASPNet MVC路由以捕获所有* aspx請求