471,319 Members | 1,601 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,319 software developers and data experts.

SQL Server Existence Checking with Sockets

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
Nov 21 '05 #1
2 1709
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

-----------------
"GreyAlien007" <Gr**********@discussions.microsoft.com> wrote in message
news:AF**********************************@microsof t.com...
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
Nov 21 '05 #2
I think that you can use either SQLBrowseConnect ODBC API function or
SQL-DMO. See
http://groups.google.es/groups?hl=es...3DN%26tab%3Dwg

--

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
"GreyAlien007" <Gr**********@discussions.microsoft.com> escribió en el
mensaje news:AF**********************************@microsof t.com...
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

Nov 21 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Richard L Rosenheim | last post: by
4 posts views Thread by Herbert VON GRÜNENWALD | last post: by
reply views Thread by Suresh | last post: by
25 posts views Thread by _DD | last post: by
reply views Thread by alikim | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.