我使用以下代码列出所有远程和本地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已打开。
有人知道为什么吗?谢谢。
非常感谢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.
最后,我只是合并了远程实例列表和本地实例列表以产生最终结果。