How to Test Database Connectivity From a Workstation?

Hey guys,

I have a client that is using a SQL database driven software. SQL and
the databases are housed on a separate server, and the software is
installed on the workstation. We use a connection string to connect
the database.

Well, they are completely down with messages, "object reference not
set to an instance of an object" and "sql server does not exist".
Does anyone know of a tool or application that I could use to see if
they can query or connect to the database from their local
workstation. They are not running SQL software on the local
workstations. I can query the database just fine when remoted to
their SQL server.

Re: How to Test Database Connectivity From a Workstation?

alvinstraight38@hotmail.com (alvinstraight38@hotmail.com) writes:
> I have a client that is using a SQL database driven software. SQL and
> the databases are housed on a separate server, and the software is
> installed on the workstation. We use a connection string to connect
> the database.
>
> Well, they are completely down with messages, "object reference not
> set to an instance of an object" and "sql server does not exist".
> Does anyone know of a tool or application that I could use to see if
> they can query or connect to the database from their local
> workstation. They are not running SQL software on the local
> workstations. I can query the database just fine when remoted to
> their SQL server.

Why not write a small console-mode program in C# or VB .Net for the
purpose?

If you want a starting point, below is something I happened to have
lying around.

Imports System.Data
Imports System.Data.SqlClient

Module Bugtest

Private Sub PrintSqlErrors(ByVal errors As SqlErrorCollection, ByVal what As String)
Dim e As SqlError
For Each e In errors
Console.WriteLine( what & _
" Message: Msg " & e.Number.ToString() & _
", Severity " & e.Class.ToString() & _
", State: " & e.State.ToString() & _
", Procedure: " & e.Procedure & _
", Line no: " & e.LineNumber.ToString & vbCrLf & _
e.Message & vbCrLf)
Next
End Sub

Private Sub OutputException(ex As Exception)
If TypeOf ex Is SqlException Then
Dim SqlEx As SqlException = DirectCast(ex, SqlException)
PrintSqlErrors(SqlEx.Errors, "Error")
Else
Console.WriteLine(ex.ToString())
End if
End Sub

Private Sub SqlInfoMessage(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs)
PrintSqlErrors(e.Errors, "INFO MSG")
End Sub

Private Sub PrintResultsets(Reader As SqlDataReader)
Dim resset_no As Integer = 1
Dim more_results As Boolean = true
If Reader Is Nothing Then
Console.Writeline("There is no reader object!")
Exit Sub
End If
Do
Console.WriteLine("================ Result set " & resset_no.ToString() & _
" == " & Now() & " ==========")
resset_no += 1
Try
Console.WriteLine("Rows affected: " & Reader.RecordsAffected.ToString)
If Reader.IsClosed Then
Console.WriteLine("------ Closed resultset -------")
ElseIf Not Reader.HasRows Then
Console.WriteLine("------ There are no rows -------")
Else
Dim no_of_col As Integer = Reader.FieldCount
Dim i As Integer
Dim Out As String = ""
For i = 0 To no_of_col - 1
Out &= Reader.GetName(i) & vbTab
Next
Console.WriteLine(Out)
Out = ""
While Reader.Read()
For i = 0 To no_of_col - 1
Out &= Reader(i).ToString & vbTab
Next
Console.WriteLine(Out)
End While
End If
Catch ex As Exception
OutputException(ex)
If Reader Is Nothing Then Exit Sub
End Try

Try
more_results = Reader.NextResult()
Catch ex As Exception
OutputException(ex)
End Try
Loop While more_results

If Not Reader Is Nothing Then Reader.Close
End Sub

Private Sub PrintSummary(Reader() As SqlDataReader)
Dim resset_no() As Integer = {1, 1}
Dim rowno() As Integer = {0, 0}
Dim more_rows() As Boolean = {True, True}
Dim more_results() As Boolean = {True, True}

Do
If more_results(0) Then
Console.WriteLine("[0] ================ Result set " & resset_no(0).ToString() & _
" == " & Now() & " ==========")
resset_no(0) += 1
End if
If more_results(1) Then
Console.WriteLine("[1] ================ Result set " & resset_no(1).ToString() & _
" == " & Now() & " ==========")
resset_no(1) += 1
End if

Try
Console.WriteLine("[0] Rows affected: " & Reader(0).RecordsAffected.ToString)
Console.WriteLine("[1] Rows affected: " & Reader(1).RecordsAffected.ToString)

If Reader(0).IsClosed Then
Console.WriteLine("[0] ------ Closed resultset -------")
more_rows(0) = False
ElseIf Not Reader(0).HasRows Then
Console.WriteLine("[0]------ There are no rows -------")
more_rows(0) = False
End if

If Reader(1).IsClosed Then
Console.WriteLine("[1] ------ Closed resultset -------")
more_rows(1) = False
ElseIf Not Reader(1).HasRows Then
Console.WriteLine("[1]------ There are no rows -------")
more_rows(1) = False
End if

While more_rows(0) Or more_rows(1)
if more_rows(0) Then
more_rows(0) = Reader(0).Read
rowno(0) += 1
if rowno(0) Mod 1000 = 1 Then
Console.Writeline("[0] " & rowno(0).ToString() & vbCrLf)
End If
End if
if more_rows(1) Then
more_rows(1) = Reader(1).Read
rowno(1) += 1
if rowno(1) Mod 1000 = 1 Then
Console.Writeline("[1] " & rowno(1).ToString() & vbCrLf)
End If
End if
End While
Catch ex As Exception
OutputException(ex)
Exit Sub
End Try

Try
more_results(0) = Reader(0).NextResult()
more_results(1) = Reader(1).NextResult()
rowno(0) = 0
rowno(1) = 0
Catch ex As Exception
OutputException(ex)
End Try
Loop While more_results(0) or more_results(1)

If Not Reader(0) Is Nothing Then Reader(0).Close
If Not Reader(1) Is Nothing Then Reader(1).Close
End Sub

Public Sub Main()

Dim cn As New SqlConnection, _
strConn As String

AddHandler cn.InfoMessage, AddressOf SqlInfoMessage

' Connection string, change server and database!
strConn = "Integrated Security=SSPI;Initial Catalog=tempdb;" & _
"MultipleActiveResultSets=True"
Try
cn.ConnectionString = strConn
cn.Open()
Catch ex As Exception
Console.Writeline(ex.Message, "Connection failed!")
cn = Nothing
Exit Sub
End Try

cn.FireInfoMessageEventOnUserErrors = True

Dim cmd1 As SqlCommand = cn.CreateCommand()
Dim cmd2 As SqlCommand = cn.CreateCommand()

cmd1.CommandType = System.Data.CommandType.Text
cmd1.CommandText = "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION; "
' cmd1.ExecuteNonQuery()


cmd1.CommandType = System.Data.CommandType.Text
cmd1.CommandText = "select * from Northwind.dbo.[Order Details] OD "
cmd1.CommandTimeout = 0
cmd2.CommandType = System.Data.CommandType.Text
cmd2.CommandText = "USE Northgale; EXECUTE AS USER = 'frits'; select count(*) from Orders O join [Order Details] OD on O.OrderID = OD.OrderID where O.EmployeeID = 8 and OD.Quantity = 12"
cmd2.CommandTimeout = 0
' cmd.Parameters.Add("@prodid", SqlDbType.Int)
' cmd.Parameters(0).Direction = ParameterDirection.Input
' cmd.Parameters(0).Value = 76

Dim Reader(2) As SqlDataReader
Try
Reader(0) = cmd1.ExecuteReader()
Reader(1) = cmd2.ExecuteReader()
Catch ex As Exception
OutputException(ex)
End Try
PrintSummary(Reader)

cn.Close()
cn.Dispose()

End Sub

End Module



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx