SQL Server Existence Checking with Sockets

[Guest]

I wanted my programs to check to see if SQL server was available...the
following is what I cam up with:

Public Function SetServerInfo(ByVal serverName As String, ByVal dbName
As String, Optional ByVal instanceName As String = "", Optional ByVal
portNumber As Int32 = 1433) As Boolean

Dim tcpSocket As Socket
Dim resolvedServer As IPHostEntry
Dim serverEndPoint As IPEndPoint
Dim addr As IPAddress

Try
resolvedServer = Dns.Resolve(serverName)
addr = resolvedServer.AddressList(0) 'May not work on a dual
home box but I don't care
serverEndPoint = New IPEndPoint(addr, portNumber)
tcpSocket = New Socket(addr.AddressFamily, SocketType.Stream,
ProtocolType.Tcp)
tcpSocket.Connect(serverEndPoint)
If instanceName <> "" Then
_sqlServer = serverName & "\" & instanceName
Else
_sqlServer = serverName
End If
_dbName = dbName
Return True
Catch ex As Exception
_ErrorMessage = "Invalid server name, instance name or port.
Server could be offline. Contact a member of the IT support staff. "
Return False
Finally
If Not IsNothing(tcpSocket) Then
tcpSocket.Close()
End If
End Try

End Function

I would like some suggestions on improvment. More specific, I don't want to
pass the port number into this function and I want to be sure that if a
connection works that it's a SQL server that's answering. The port should be
able to be determined by some sort of port scan or something, and I would
imagine to accomplish the second thing I would need to send some sort of
packet in a way that would tell SQL to send me back a packet (I have no idea,
just random guesses).

Thanks

 

[Ken Tucker [MVP]]

Hi,

You can use the sqldmo object library to list available servers. Add
a reference to microsoft sqldmo object library in the com tab. Maybe this
will help.

Dim sqlServers As SQLDMO.NameList

Dim sqlServer As String

' Get a list of servers

sqlServers = New SQLDMO.Application().ListAvailableSQLServers

For Each sqlServer In sqlServers

Debug.WriteLine(sqlServer)

Next



Ken

-----------------


I wanted my programs to check to see if SQL server was available...the
following is what I cam up with:

Public Function SetServerInfo(ByVal serverName As String, ByVal dbName
As String, Optional ByVal instanceName As String = "", Optional ByVal
portNumber As Int32 = 1433) As Boolean

Dim tcpSocket As Socket
Dim resolvedServer As IPHostEntry
Dim serverEndPoint As IPEndPoint
Dim addr As IPAddress

Try
resolvedServer = Dns.Resolve(serverName)
addr = resolvedServer.AddressList(0) 'May not work on a dual
home box but I don't care
serverEndPoint = New IPEndPoint(addr, portNumber)
tcpSocket = New Socket(addr.AddressFamily, SocketType.Stream,
ProtocolType.Tcp)
tcpSocket.Connect(serverEndPoint)
If instanceName <> "" Then
_sqlServer = serverName & "\" & instanceName
Else
_sqlServer = serverName
End If
_dbName = dbName
Return True
Catch ex As Exception
_ErrorMessage = "Invalid server name, instance name or port.
Server could be offline. Contact a member of the IT support staff. "
Return False
Finally
If Not IsNothing(tcpSocket) Then
tcpSocket.Close()
End If
End Try

End Function

I would like some suggestions on improvment. More specific, I don't want to
pass the port number into this function and I want to be sure that if a
connection works that it's a SQL server that's answering. The port should
be
able to be determined by some sort of port scan or something, and I would
imagine to accomplish the second thing I would need to send some sort of
packet in a way that would tell SQL to send me back a packet (I have no
idea,
just random guesses).

Thanks

 

[Carlos J. Quintero [.NET MVP]]

I think that you can use either SQLBrowseConnect ODBC API function or
SQL-DMO. See
http://groups.google.es/groups?hl=e...iscover%20SQL%20Servers&hl=es&lr=&sa=N&tab=wg

--

Carlos J. Quintero

MZ-Tools 4.0: Productivity add-ins for Visual Studio .NET
You can code, design and document much faster.
http://www.mztools.com