On Dec 19, 12:33 pm, Peter <Pe...@discussi ons.microsoft.c omwrote:
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\SQLEX PRESS
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.
"Peter" wrote:
I'm trying to get a list of SQL Server Instances thru a VB.NET application.
I have tried GetDataSource and SMO. I have also tried using ListSQLSvr.exe
fromhttp://www.sqldev.net/misc/ListSQLSvr.htm
I run all of them on a Windows XP Professional 64bit machine which has 2
instances: mymachine\SQLEx press (Express Edition) and MSSQLSERVER (Developer
Edition (64bit))
SQL Browser Service: not running
- GetDataSource or SMO returns MSSQLSERVER
- ListSQLSvr returns nothing
SQL Browser Service: running
- GetDataSource or SMO or ListSQLSvr returns mymachine\SQLEX PRESS- Hide quoted text -
- 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.Collecti ons.Generic;
using System.Text;
using System.Net;
using System.Data;
using System.Data.Sql ;
using Microsoft.Win32 ;
using Microsoft.SqlSe rver.Management .Smo;
namespace FireAnt.Control s.Database
{
[Serializable()]
internal class ServerInstance : IComparable,
IComparable<Ser verInstance>, IComparable<str ing>
{
private string server;
private string instance;
private Version version;
private static readonly string LocalServer = Dns.GetHostName
().ToUpper ();
private const string ServerInstanceR egistryKey = "SOFTWARE\
\MICROSOFT\\MIC ROSOFT SQL SERVER\\INSTANC E 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.IsNullO rEmpty ( this.Instance ) )
{
displayString.A ppendFormat ( "\\{0}", this.Instance );
}
// add the version if it is a valid one
if ( this.Version.Ma jor != 0 )
{
displayString.A ppendFormat ( " ({0}.{1})",
this.Version.Ma jor, this.Version.Mi nor );
}
return displayString.T oString ();
}
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.ToStri ng ();
}
#endregion
public static List<ServerInst anceGetLocalSer verList ()
{
List<ServerInst ancelocalServer s = new
List<ServerInst ance();
RegistryKey registryKey = Registry.LocalM achine.OpenSubK ey
( ServerInstanceR egistryKey );
if ( registryKey != null )
{
using ( registryKey )
{
foreach ( string instanceName in
registryKey.Get ValueNames () )
{
localServers.Ad d ( new ServerInstance
( LocalServer, instanceName ) );
}
}
}
return localServers;
}
public static List<ServerInst anceGetNetworkS erverList ()
{
List<ServerInst ancenetworkServ ers = new
List<ServerInst ance();
using ( DataTable dataSources =
SqlDataSourceEn umerator.Instan ce.GetDataSourc es () )
{
foreach ( DataRow dataSource in dataSources.Row s )
{
networkServers. Add ( new ServerInstance (
(string)dataSou rce["ServerName "],
Convert.IsDBNul l
( dataSource["InstanceNa me"] ) ? string.Empty :
(string)dataSou rce["InstanceNa me"],
Convert.IsDBNul l ( dataSource["Version"] ) ?
"0.0" : (string)dataSou rce["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<Ser verInstanceMemb ers
public int CompareTo ( ServerInstance other )
{
return this.ToString ().CompareTo ( other.ToString () );
}
#endregion
#region IComparable<str ingMembers
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 :)
--
Tom Shelton
