Additional Info:
If I try to add connection in Server Explorer of VS2008,
- SQL Browser Service: not running
only see MSSQLSERVER
- SQL Browser Service: running
only see mymachine\SQLEXPRESS
So, this behavior is same as GetDataSource or SMO.
I hope someone can explain this strange behavior and tell me how I should
get the list of SQL Server Instances in a VB.Net application.
- Show quoted text -
Here's some C# code I wrote for a dialog box component that mimics the
one from SQL Management Studio... It seems to work for me:
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Data;
using System.Data.Sql;
using Microsoft.Win32;
using Microsoft.SqlServer.Management.Smo;
namespace FireAnt.Controls.Database
{
[Serializable()]
internal class ServerInstance : IComparable,
IComparable<ServerInstance>, IComparable<string>
{
private string server;
private string instance;
private Version version;
private static readonly string LocalServer = Dns.GetHostName
().ToUpper ();
private const string ServerInstanceRegistryKey = "SOFTWARE\
\MICROSOFT\\MICROSOFT SQL SERVER\\INSTANCE NAMES\\SQL";
#region Constructors
public ServerInstance () : this ( string.Empty ) { }
public ServerInstance ( string server ) : this ( server,
string.Empty ) { }
public ServerInstance ( string server, string instance ) :
this ( server, instance, "0.0" ) { }
public ServerInstance ( string server, string instance, string
version ) : this ( server, instance, new Version ( version ) ) { }
public ServerInstance ( string server, string instance,
Version version )
{
this.server = server;
this.instance = instance;
this.version = version;
}
#endregion
#region Properties
public string Server
{
get { return this.server; }
}
public string Instance
{
get { return this.instance; }
}
public Version Version
{
get { return this.version; }
}
#endregion
#region Overrides
public override string ToString ()
{
StringBuilder displayString = new StringBuilder
( this.Server );
// add the instance name if we have one
if ( !string.IsNullOrEmpty ( this.Instance ) )
{
displayString.AppendFormat ( "\\{0}", this.Instance );
}
// add the version if it is a valid one
if ( this.Version.Major != 0 )
{
displayString.AppendFormat ( " ({0}.{1})",
this.Version.Major, this.Version.Minor );
}
return displayString.ToString ();
}
public override bool Equals ( object obj )
{
if ( obj == null || !( obj is ServerInstance ) )
return false;
return ( this.ToString () == obj.ToString () );
}
public override int GetHashCode ()
{
return this.ToString ().GetHashCode ();
}
#endregion
#region Operators
public static implicit operator string ( ServerInstance
instance )
{
return instance.ToString ();
}
#endregion
public static List<ServerInstance> GetLocalServerList ()
{
List<ServerInstance> localServers = new
List<ServerInstance> ();
RegistryKey registryKey = Registry.LocalMachine.OpenSubKey
( ServerInstanceRegistryKey );
if ( registryKey != null )
{
using ( registryKey )
{
foreach ( string instanceName in
registryKey.GetValueNames () )
{
localServers.Add ( new ServerInstance
( LocalServer, instanceName ) );
}
}
}
return localServers;
}
public static List<ServerInstance> GetNetworkServerList ()
{
List<ServerInstance> networkServers = new
List<ServerInstance> ();
using ( DataTable dataSources =
SqlDataSourceEnumerator.Instance.GetDataSources () )
{
foreach ( DataRow dataSource in dataSources.Rows )
{
networkServers.Add ( new ServerInstance (
(string)dataSource["ServerName"],
Convert.IsDBNull
( dataSource["InstanceName"] ) ? string.Empty :
(string)dataSource["InstanceName"],
Convert.IsDBNull ( dataSource["Version"] ) ?
"0.0" : (string)dataSource["Version"] ) );
}
}
return networkServers;
}
#region IComparable Members
public int CompareTo ( object obj )
{
if ( obj == null || !(obj is ServerInstance ))
return -1;
else
return this.ToString ().CompareTo ( obj.ToString () );
}
#endregion
#region IComparable<ServerInstance> Members
public int CompareTo ( ServerInstance other )
{
return this.ToString ().CompareTo ( other.ToString () );
}
#endregion
#region IComparable<string> Members
public int CompareTo ( string other )
{
return this.ToString ().CompareTo ( other );
}
#endregion
}
}
Anyway, maybe you can make use of this - convert it to vb or
whatever
