Server list not returning correctly

[Solarion]

I have this neat little method to find the SQL instances on a local machine and display them in a list.

Private serverItems As New List(Of String)

Public Sub CheckServers() Handles bgworker.DoWork
        Try


            Dim instance As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance
            Dim table As System.Data.DataTable = instance.GetDataSources()
            For Each row As System.Data.DataRow In table.Rows
                If Not IsDBNull("ServerName") AndAlso Environment.MachineName.Equals(row("ServerName").ToString()) Then
                    Dim item As String = String.Empty
                    item = row("ServerName").ToString()
                    If Not IsDBNull(row("InstanceName")) OrElse Not String.IsNullOrEmpty(Convert.ToString(row("InstanceName")).Trim()) Then
                        item += "\" + Convert.ToString(row("InstanceName")).Trim()
                    End If

                    serverItems.Add(item.ToString())
                End If
            Next
        Catch ex As Exception
            MessageBox.Show("No Servers Found.")
        End Try
    End Sub

One problem. My server is called "DEV1\SQLEXPRESS" but this method only returns "DEV1"

Any thoughts? Perhaps the server is named wrong?

Edit: References:

Imports System.Data.Sql
Imports System.ComponentModel

 

[Darko]

Step through your code and see what the value of item is before and after this line:

item += "" + Convert.ToString(row("InstanceName")).Trim()

then add Convert.ToString(row("InstanceName")).Trim() to quick watch to see what's happening there.

Why are you converting instance to string? It's already a string? And it won't have spaces?

 

[halfmoonforever]

gewgs

http://stackoverflow.com/questions/31996058/net-not-detecting-any-sql-server-instances

 

[Solarion]

Good call on the strings. I've commented out the check for servers and simply added it to the list. Still nothing. It's as if there are no Instances installed.

Public Sub CheckServers() Handles BackgroundWorker1.DoWork
        Try


            Dim instance As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance
            Dim table As System.Data.DataTable = instance.GetDataSources()
            For Each row As System.Data.DataRow In table.Rows
                If Not IsDBNull("ServerName") AndAlso Environment.MachineName.Equals(row("ServerName")) Then
                    Dim item As String = String.Empty
[B]                    item = row("ServerName")
                    'If Not IsDBNull(row("InstanceName")) OrElse Not String.IsNullOrEmpty(Convert.ToString(row("InstanceName")).Trim()) Then
                    item += "" + (row("InstanceName"))
                    'End If[/B]

                    serverItems.Add(item.ToString())
                End If
            Next
        Catch ex As Exception
            MessageBox.Show("No Servers Found.")
        End Try
    End Sub

 

[LPCPT]

This may be a long shot, but is backslash not an escape character. In C# I used to make it double backslash. Like when you use filepaths etc.
Even typing it in here looses the "backslash" character

 

[Alton Turner Blackwood]

This may be a long shot, but is backslash not an escape character. In C# I used to make it double backslash. Like when you use filepaths etc.
Even typing it in here looses the "backslash" character

This man ^ knows his schit!

 

[Solarion]

This may be a long shot, but is backslash not an escape character. In C# I used to make it double backslash. Like when you use filepaths etc.
Even typing it in here looses the "backslash" character

I wish that was the problem. Anyway I'm going to try the ODBC route.

http://www.codeproject.com/Articles/10156/Enumerate-SQL-Server-Instances-in-C-Using-ODBC

 

[Darko]

I wish that was the problem. Anyway I'm going to try the ODBC route.

Have you tried C#?

 

[schuits]

It works fine when I try it. Only I removed

If Not IsDBNull("ServerName") AndAlso Environment.MachineName.Equals(row("ServerName").ToString()) Then

Shouldn't this be

If Not IsDBNull(row("ServerName")) AndAlso Environment.MachineName.Equals(row("ServerName").ToString()) Then

 

[Solarion]

Have you tried C#?

Not sure the outcome would be any different. It seems to be a SQL 2014 issues. Something to do with SMO's and machine objects, some or other jargon like that.

It works fine when I try it. Only I removed

If Not IsDBNull("ServerName") AndAlso Environment.MachineName.Equals(row("ServerName").ToString()) Then

Shouldn't this be

If Not IsDBNull(row("ServerName")) AndAlso Environment.MachineName.Equals(row("ServerName").ToString()) Then

Works fine over a network. Picks up all the server names + instance names. On a local machine though its balls. Only picks up the server name "DEV1/" and not "DEV1/SQLEXPRESS"