List of SQL Instances...

[lidds]

I am currently using the below code to get a list of SQL instances, however the problem is that the code below get's all SQL instances on the whole of a network. I am currently writing a wizard for my application, this wizard is installed on the server that they have SQL Server installed. Therefore I do not want the code to go and find all the SQL instances on a network, but just the SQL instance(s) on the current machine. Could someone people help me in showing me want I need to modify in the below code to do this?

Dim sqlList As SQLDMO.NameList = Nothing
        Dim sqlApp As SQLDMO.Application = Nothing

        Try
            sqlApp = New SQLDMO.Application
            sqlList = sqlApp.ListAvailableSQLServers

            Dim colSQLServer As ComboBoxItemCollection = Me.cmbSQLServer.Properties.Items

            Dim ctr As Integer = 1
            While ctr <= sqlList.Count
                colSQLServer.Add(New String(sqlList.Item(ctr)))
                System.Math.Min(System.Threading.Interlocked.Increment(ctr), ctr - 1)
            End While

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            If Not (sqlList Is Nothing) Then
                sqlList = Nothing
                Me.cmbSQLServer.SelectedIndex = 0
            End If

            If Not (sqlApp Is Nothing) Then
                sqlApp = Nothing
            End If
        End Try

Thanks in advance

Simon

 

[MattP]

This worked for me:

		Dim instance As System.Data.Sql.SqlDataSourceEnumerator = _
		 System.Data.Sql.SqlDataSourceEnumerator.Instance

		Dim localInstances() As DataRow = _
		 instance.GetDataSources.Select(String.Format("ServerName='{0}'", "ServerNameHere"))

Edit: Add msdn link for reference Enumerating Instances of SQL Server (ADO.NET)

 

[lidds]

Thanks MattP,

However just a couple of things,

1. Is there a way to get the servername of the machine that my app is running on?
2. How can I get the actual SQL Instance names from your code, e.g. how can I loop through the DataRow and return the SQL Instance value?
3. This seems to still take sometime, is this still search the entire network and just omitting the other SQL Instances return from the other servers? Or is it just searching the local machine? If it is searching the entire network, is there a way to stop this and just search the local machine. The reason why I ask this is because this app is going to be used in companies that could have a network as large as 2000+ machines and it would obviously take ages to search through them.

Sorry for some very basic questions am still quite new to the whole VB.Net code.

Thanks in advance

Simon

 

[MattP]

You're right that it does enumerate instances from all of the machines on the network and filter it afterwards.

I decided to check pulling the information out of the registry to get around this.

		Dim regKey As Microsoft.Win32.RegistryKey = _
		 Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL")
		Dim instances() As String = DirectCast(regKey.GetValueNames(), String())

To get the local system name you can use My.Computer.Name.