-
Dec 19th, 2017, 01:02 AM
#1
Thread Starter
Member
[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?
-
Dec 19th, 2017, 01:18 AM
#2
Re: Get all SQLExpress Instance Names in a small local Network
Originally Posted by gbhsk
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.
-
Dec 20th, 2017, 09:36 AM
#3
Thread Starter
Member
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.
-
Dec 20th, 2017, 09:42 AM
#4
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
-
Dec 20th, 2017, 09:49 AM
#5
Thread Starter
Member
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
-
Dec 20th, 2017, 09:59 AM
#6
Thread Starter
Member
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
-
Dec 20th, 2017, 10:42 AM
#7
Re: Get all SQLExpress Instance Names in a small local Network
Originally Posted by gbhsk
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
-
Dec 20th, 2017, 11:31 AM
#8
Thread Starter
Member
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
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.
-
Dec 20th, 2017, 12:04 PM
#9
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
-
Dec 20th, 2017, 06:23 PM
#10
Thread Starter
Member
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|