Results 1 to 10 of 10

Thread: [RESOLVED] Get all SQLExpress Instance Names in a small local Network

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2016
    Posts
    43

    Resolved [RESOLVED] Get all SQLExpress Instance Names in a small local Network

    Hi
    When I do
    Code:
            Dim dt As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources
                      For Each dr As DataRow In dt.Rows
                            lstInstances.Items.Add(String.Concat(dr("ServerName"), "\", dr("InstanceName")))
                      Next
    I get

    PC1\SQLExpress
    PC2\
    I have learnt that PC2 is then running the default instance.
    Problem: How do I know if PC2 is running an Express edition or standard edition or enterprise edition. How can I get the instancename in PC2. Something like

    Code:
    IF dr("InstanceName").tostring.equals(string.empty) then
                  Find InstanceName via Registry (say)
             End IF
    I need to get only the Express editions.

    I have also thought of scanning registries for Instancenames like
    Code:
    Public Function GetLAN_SQLExpress_Instances() as Datatable
    	dim DtPCnames as datatable = GetPCNames
    
    	For each Rw as datarow in DtPCnames.Rows
    			If Rw(Names)=My.Computer.Name then    'checking local PC for Express Instances
    				if 64 bit OS then check localInstancename for Express Instances using registry
    				if 32 bit OS then check localInstancenames for Express Instances using registry
    			else                                  'checking remote PC for Express Instances
    				if 64 bit OS then check remoteInstancenames for Express Instances using registry
    				if 32 bit OS then check remoteInstancenames for Express Instances using registry
    			End IF
    	dt.rows.add(Rw("Names"),Rw(Instance))
    	Next
    
    	return dtInstance
    
    End Function
    but I need to learn how use local and remote registries of Microsoft.Win32(never mind the risk).

    Any ideas how to get InstanceName of a default Instance?

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Get all SQLExpress Instance Names in a small local Network

    Quote Originally Posted by gbhsk View Post
    Any ideas how to get InstanceName of a default Instance?
    There is no instance name. A named instance has a name and a default instance doesn't. That said, the name MSSQLSERVER is used for a default instance in some places, e.g. in the relevant Windows service names.

    There is no way to do what you want using SqlDataSourceEnumerator. It will give you the version but not the edition. If everyone has followed the convention then all Express Edition instances will be named SQLExpress but there's no inherent guarantee of that. I think that you'd have to use SQL Server Management Objects (SMO) to get the information you want.

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2016
    Posts
    43

    Re: Get all SQLExpress Instance Names in a small local Network

    Ok Thanks
    So How do I use SMO to get it? never used those dlls.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Get all SQLExpress Instance Names in a small local Network

    Loop through each server/instance, connect to it, get the server properties, disconnect, the Server version/edition should be a part of the properties that's returned. It may be embedded in the version numbers though and take some translation.

    Actually - it looks like it's a first class property on the class https://msdn.microsoft.com/en-us/lib...r.version.aspx

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Member
    Join Date
    Sep 2016
    Posts
    43

    Re: Get all SQLExpress Instance Names in a small local Network

    And so this method by Techgnome checks only local registries but not remote registries.
    How can I add remote registry check to cater for instances in other network clients

    Code:
      Private Sub LoadSQLServers(ByVal sqlCombo As ComboBox)
                Dim sqlServerList As List(Of String) = New List(Of String)
    
                sqlCombo.Items.Clear()
    
                Try
                    Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)
                    If dt.Rows.Count > 0 Then
                        For Each dr As DataRow In dt.Rows
                            If Not sqlServerList.Contains(dr("Name").ToString) Then
                                sqlServerList.Add(dr("Name").ToString)
                            End If
                        Next
                    End If
                Catch ex As Exception
                    LogEvent(ex.ToString)
                End Try
    
                Try
                    Dim rk As RegistryKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server")
                    Dim instances As String() = CType(rk.GetValue("InstalledInstances"), String())
                    If (instances.Length > 0) Then
                        For Each element As String In instances
                            If element = "MSSQLSERVER" Then
                                If Not sqlServerList.Contains(System.Environment.MachineName) Then
                                    sqlServerList.Add(System.Environment.MachineName)
                                End If
                            Else
                                If Not sqlServerList.Contains(System.Environment.MachineName + "\" + element) Then
                                    sqlServerList.Add(System.Environment.MachineName + "\" + element)
                                End If
                            End If
                        Next
                    End If
                Catch ex As Exception
                    LogEvent(ex.ToString)
                Finally
                    sqlCombo.DataSource = sqlServerList
                End Try
    
            End Sub

  6. #6

    Thread Starter
    Member
    Join Date
    Sep 2016
    Posts
    43

    Re: Get all SQLExpress Instance Names in a small local Network

    Alternatively there a very good powershell script here which does exactly what I want.
    Problem is How can I wrap such a script into VB.net code and get the results into a datatable (say)
    Thanks

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Get all SQLExpress Instance Names in a small local Network

    Quote Originally Posted by gbhsk View Post
    And so this method by Techgnome checks only local registries but not remote registries.
    How can I add remote registry check to cater for instances in other network clients

    Code:
      Private Sub LoadSQLServers(ByVal sqlCombo As ComboBox)
                Dim sqlServerList As List(Of String) = New List(Of String)
    
                sqlCombo.Items.Clear()
    
                Try
                    Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)
                    If dt.Rows.Count > 0 Then
                        For Each dr As DataRow In dt.Rows
                            If Not sqlServerList.Contains(dr("Name").ToString) Then
                                sqlServerList.Add(dr("Name").ToString)
                            End If
                        Next
                    End If
                Catch ex As Exception
                    LogEvent(ex.ToString)
                End Try
    
                Try
                    Dim rk As RegistryKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server")
                    Dim instances As String() = CType(rk.GetValue("InstalledInstances"), String())
                    If (instances.Length > 0) Then
                        For Each element As String In instances
                            If element = "MSSQLSERVER" Then
                                If Not sqlServerList.Contains(System.Environment.MachineName) Then
                                    sqlServerList.Add(System.Environment.MachineName)
                                End If
                            Else
                                If Not sqlServerList.Contains(System.Environment.MachineName + "\" + element) Then
                                    sqlServerList.Add(System.Environment.MachineName + "\" + element)
                                End If
                            End If
                        Next
                    End If
                Catch ex As Exception
                    LogEvent(ex.ToString)
                Finally
                    sqlCombo.DataSource = sqlServerList
                End Try
    
            End Sub
    Nothing in that code is anything I suggested. At all. It's only checking local instances because that's all you're checking. The Registry you opened is the list of local SQL Servers. So of course it isn't going to check others. The first loop should be closer to what you want... should be returning the list of SQL Servers on the network... it may or may not work... it's known to be a bit wonky as it depends on the individual servers being responsive to the request.

    I gave you the logic to get the info. You have to get a list of the servers running SQL Server... and SMO will do that. Then you have to CONNECT to EACH SERVER/INSTANCE and ask it for it's version ... using the link I supplied will help with that. When you connect to the server, the .Version property should then hold what version of SQL Server you connected to.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Member
    Join Date
    Sep 2016
    Posts
    43

    Re: Get all SQLExpress Instance Names in a small local Network

    Ok I just did the following on my laptop( not in network) with one instance
    Code:
    Imports Microsoft.SqlServer.Management.Sdk.Sfc
    Imports Microsoft.SqlServer.Management.Smo
    Imports System.Windows.Forms
    Imports System.Data
    Imports System
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dataTable As DataTable = SmoApplication.EnumAvailableSqlServers(False)
            dgv.DataSource = dataTable
        End Sub
    End Class
    and got
    Name:  GetsqlInstances.jpg
Views: 2278
Size:  13.9 KB

    if the editions were included it would be very helpful because what I really want is:
    A list of servers ,instances, edition is displayed. Express editions are filtered. User then chooses the express instance he/she needs to connect to. Done.
    Perhaps what I actually meant by version was actually the Editions. HaHaha Just learning.
    Last edited by gbhsk; Dec 20th, 2017 at 11:38 AM.

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Get all SQLExpress Instance Names in a small local Network

    Right I got that. I'm going to say this one more time:
    You have to get a list of the servers running SQL Server... and SMO will do that. -- Done... you have that.
    Then you have to CONNECT to EACH SERVER/INSTANCE and ask it for it's version -- this is the next step... loop through the datatable you've got, connect to the server, then get the version -- edit there's also an Edition property ... https://msdn.microsoft.com/en-us/lib...r.edition.aspx -- all you have to do is just connect to the server and get the property value.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Member
    Join Date
    Sep 2016
    Posts
    43

    Re: Get all SQLExpress Instance Names in a small local Network

    This time I got it with much help from here.

    And here is my working code that can help someone.

    Code:
    Imports Microsoft.SqlServer.Management.Sdk.Sfc
    Imports Microsoft.SqlServer.Management.Smo
    Imports System.Windows.Forms
    Imports System.Data
    Imports System
    Imports Microsoft.SqlServer.Management.Common
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)
            'dgv.DataSource = dt
    
            With dgv
                .Columns.Add("Name", "Name")
                .Columns.Add("Server", "Server")
                .Columns.Add("Instance", "Instance")
                .Columns.Add("Edition", "Edition")
                .AllowUserToAddRows = False
            End With
            If dt.Rows.Count > 0 Then
                'fill dgv without edition
                For i As Integer = 0 To dt.Rows.Count - 1
                    Dim rw As String() = New String() { _
                    dt.Rows(i)("Name").ToString,
                    dt.Rows(i)("Server").ToString,
                    dt.Rows(i)("Instance").ToString, String.Empty}
                    dgv.Rows.Add(rw)
                Next
                'get editions
                For m As Integer = 0 To dgv.Rows.Count - 1
                    Dim sqlServerLogin As [String] = "sa"  '(for example)
                    Dim password As [String] = "mypwd"      '(for example)
                    Dim instanceName As [String] = Nothing
                    If Not dgv.Rows(m).Cells("Instance").Value Is DBNull.Value Then
                        instanceName = dgv.Rows(m).Cells("Instance").Value.ToString
                    Else
                        instanceName = String.Empty
                    End If
                    Dim SvrName As [String] = dgv.Rows(m).Cells("Server").Value.ToString
    
                    If SvrName = My.Computer.Name Then 'local instances
    
                        If instanceName.ToString.Equals("") Then
                            'default instance
                            Dim srv1 As New Server()
                            srv1.ConnectionContext.LoginSecure = False   ' set to true for Windows Authentication
                            srv1.ConnectionContext.Login = sqlServerLogin
                            srv1.ConnectionContext.Password = password
                            dgv.Rows(m).Cells("Edition").Value = srv1.Information.Edition
                        Else
                            'named instance
                            Dim srvConn As New ServerConnection()
                            srvConn.ServerInstance = SvrName & "\" & instanceName
                            srvConn.LoginSecure = False
                            srvConn.Login = sqlServerLogin
                            srvConn.Password = password
                            Dim srv2 As New Server(srvConn)
                            dgv.Rows(m).Cells("Edition").Value = srv2.Information.Edition
                        End If
    
                    Else   'remote instances
    
                        Dim remoteSvrName As [String] =
                        dgv.Rows(m).Cells("Server").Value.ToString
    
                        Dim srvConn2 As New ServerConnection(remoteSvrName)
                        srvConn2.LoginSecure = False
                        srvConn2.Login = sqlServerLogin
                        srvConn2.Password = password
                        Dim srv3 As New Server(srvConn2)
                        dgv.Rows(m).Cells("Edition").Value = srv3.Information.Edition
    
                    End If
                Next
            Else
                MsgBox("No SQL Instances found.")
            End If
        End Sub
    End Class
    Name:  Revised result.jpg
Views: 2226
Size:  16.3 KB

    with this code the user can now know if the default instance an express edition or not.
    You have been very helpful

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width