|
|
Posted by Erland Sommarskog on 07/23/07 22:05
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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|