I have a problem: Access to a database on SQL2000 is blocking after many hou
rs running a application developt with vb.net 2003, after a time this blocki
ng is gone and there is a normal acces to this database.
The application is used for logging parameters, counters and other for a par
t of a factory and is also used for controlling machines. If there is a bloc
king then there is no possibility control this machines adequate.
The connection-string: SQL_CONNECTION_STRING = "Initial Catalog=PTSB
A02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
The vb module i use:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Module SQL
Public SQL_CONNECTION_STRING As String
'Structuur van de resultaat array
'Aantal velden is resultaat(0).aantalvelden ->
integer
'Aantal records is resultaat(0).aantalrecords ->
integer
'De veldnamen zijn resultaat(0).veldnamen(0..aantalvelden) ->
string
'De veldinhoud is resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) ->
object
Structure Resultaat_
Dim AantalVelden As Integer ' aantal velden in de recordset
Dim AantalRecords As Integer ' aantal records in de recordset
Dim VeldNamen() As String ' namen van de velden
Dim VeldInhoud() As Object ' inhoud van de velden
End Structure
' Resultaat array is de inhoud van de query uitvoering
Public Resultaat() As Resultaat_
Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As Boo
lean = False) As Boolean
If upd_Q = True Then
GeenResultaat(strSQL)
End If
If upd_Q = False Then
WelResultaat(strSQL)
End If
End Function
Private Sub GeenResultaat(ByVal queryStr As String)
Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection1.Open()
Dim myCommand As SqlCommand = myConnection1.CreateCommand()
Dim myTrans As SqlTransaction
' Start a local transaction
myTrans = myConnection1.BeginTransaction()
' Must assign both transaction object and connection
' to Command object for a pending local transaction
myCommand.Connection = myConnection1
myCommand.Transaction = myTrans
myCommand.CommandTimeout = 1
Try
myCommand.CommandText = queryStr
myCommand.ExecuteNonQuery()
myTrans.Commit()
Catch e As Exception
Try
myTrans.Rollback()
Catch ex As SqlException
If Not myTrans.Connection Is Nothing Then
Debug.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If
End Try
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while inserting the data. " & Now())
Debug.WriteLine("Neither record was written to database.")
Finally
y = y + 1
myCommand.Dispose()
myConnection1.Close()
myConnection1 = Nothing
End Try
End Sub
Private Sub WelResultaat(ByVal queryStr As String)
Dim lRow As Integer = 0 ' recordnummer
Dim lFIELD As Integer = 0 ' kolomnummer
Dim lRecordsAffected As Integer = 0 ' aantal records
Dim LFieldsAffected As Integer = 0 ' aantal velden
Dim myDataR As SqlDataReader
Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection2.Open()
Dim myCommand As SqlCommand = myConnection2.CreateCommand()
myCommand.Connection = myConnection2
Try
myCommand.CommandText = queryStr
myCommand.CommandTimeout = 2
myDataR = myCommand.ExecuteReader()
'haal het aantal velden op van de recordset
LFieldsAffected = myDataR.FieldCount
'redimensioneer de dynamische array
ReDim Resultaat(1)
ReDim Resultaat(0).VeldNamen(LFieldsAffected)
ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
'zet het aantal velden in resultaat(0).aantalvelden
Resultaat(0).AantalVelden = LFieldsAffected
'Vul de veldnamen in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
Next
'Vul de inhoud van de query uitkomst in
If myDataR.HasRows() Then
While myDataR.Read
lRow = lRow + 1
ReDim Preserve Resultaat(lRow + 1)
ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
'Vul de velden in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
Next
End While
End If
'Vul het aantal records in
Resultaat(0).AantalRecords = lRow
Catch e As Exception
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while reading the data. " & Now())
Debug.WriteLine("Neither record was read from the database.")
Finally
z = z + 1
myCommand.Dispose()
myDataR.Close()
myConnection2.Close()
myConnection2 = Nothing
End Try
End Sub
End Module
a insert query i use:
SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing, commentaar,
begin_, einde_, monteur)" & _
" VALUES (" & MM02_Inv_Stor & ", 'MM02', 'JAM op baan', 'einde storing', '--
--', '" & Format(Now(), "dd-MM-yyyy HH:mm:ss") & "', '--')", True)
a update query i use:
SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" & Mm00_Error_Bi
ts & " where NomItem='" & "lezen02" , True)
and finally a select query:
SQL.ExeQuery("SELECT * FROM mm_telling", False)
every 5 seconds i write abouth 120 update and insert queries and abouth 150
select queries.
the most tables in this database are a few rows (abouth 10 to 20)
The server is MSSQL 2000 with SP 3a
I hope there is a solution for this problem
Martin IJzermanMartin
A certain amount of blocking is normal and unavoidable.
Do you have long-running queries?
Do you cancel the query but not rolling them back?
"MartinY" <m_ijzerman@.hotmail.com> wrote in message
news:F4A1B423-A225-42C3-A02D-858791A503AD@.microsoft.com...
> I have a problem: Access to a database on SQL2000 is blocking after many
hours running a application developt with vb.net 2003, after a time this
blocking is gone and there is a normal acces to this database.
> The application is used for logging parameters, counters and other for a
part of a factory and is also used for controlling machines. If there is a
blocking then there is no possibility control this machines adequate.
> The connection-string: SQL_CONNECTION_STRING = "Initial
Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
> The vb module i use:
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
> Module SQL
> Public SQL_CONNECTION_STRING As String
> 'Structuur van de resultaat array
> 'Aantal velden is
-> integer
> 'Aantal records is
-> integer
> 'De veldnamen zijn
ldnamen(0..aantalvelden) -> string
> 'De veldinhoud is
resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) -> object
> Structure Resultaat_
> Dim AantalVelden As Integer ' aantal velden in de recordset
> Dim AantalRecords As Integer ' aantal records in de recordset
> Dim VeldNamen() As String ' namen van de velden
> Dim VeldInhoud() As Object ' inhoud van de velden
> End Structure
> ' Resultaat array is de inhoud van de query uitvoering
> Public Resultaat() As Resultaat_
>
> Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As
Boolean = False) As Boolean
> If upd_Q = True Then
> GeenResultaat(strSQL)
> End If
> If upd_Q = False Then
> WelResultaat(strSQL)
> End If
> End Function
> Private Sub GeenResultaat(ByVal queryStr As String)
> Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection1.Open()
> Dim myCommand As SqlCommand = myConnection1.CreateCommand()
> Dim myTrans As SqlTransaction
> ' Start a local transaction
> myTrans = myConnection1.BeginTransaction()
> ' Must assign both transaction object and connection
> ' to Command object for a pending local transaction
> myCommand.Connection = myConnection1
> myCommand.Transaction = myTrans
> myCommand.CommandTimeout = 1
> Try
> myCommand.CommandText = queryStr
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
> Catch e As Exception
> Try
> myTrans.Rollback()
> Catch ex As SqlException
> If Not myTrans.Connection Is Nothing Then
> Debug.WriteLine("An exception of type " &
ex.GetType().ToString() & _
> " was encountered while attempting to roll
back the transaction.")
> End If
> End Try
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while inserting the data. " &
Now())
> Debug.WriteLine("Neither record was written to database.")
> Finally
> y = y + 1
> myCommand.Dispose()
> myConnection1.Close()
> myConnection1 = Nothing
> End Try
> End Sub
> Private Sub WelResultaat(ByVal queryStr As String)
> Dim lRow As Integer = 0 ' recordnummer
> Dim lFIELD As Integer = 0 ' kolomnummer
> Dim lRecordsAffected As Integer = 0 ' aantal records
> Dim LFieldsAffected As Integer = 0 ' aantal velden
> Dim myDataR As SqlDataReader
> Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection2.Open()
> Dim myCommand As SqlCommand = myConnection2.CreateCommand()
> myCommand.Connection = myConnection2
> Try
> myCommand.CommandText = queryStr
> myCommand.CommandTimeout = 2
> myDataR = myCommand.ExecuteReader()
> 'haal het aantal velden op van de recordset
> LFieldsAffected = myDataR.FieldCount
> 'redimensioneer de dynamische array
> ReDim Resultaat(1)
> ReDim Resultaat(0).VeldNamen(LFieldsAffected)
> ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
> 'zet het aantal velden in resultaat(0).aantalvelden
> Resultaat(0).AantalVelden = LFieldsAffected
> 'Vul de veldnamen in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
> Next
> 'Vul de inhoud van de query uitkomst in
> If myDataR.HasRows() Then
> While myDataR.Read
> lRow = lRow + 1
> ReDim Preserve Resultaat(lRow + 1)
> ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
> 'Vul de velden in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
> Next
> End While
> End If
> 'Vul het aantal records in
> Resultaat(0).AantalRecords = lRow
> Catch e As Exception
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while reading the data. " & Now())
> Debug.WriteLine("Neither record was read from the database.")
> Finally
> z = z + 1
> myCommand.Dispose()
> myDataR.Close()
> myConnection2.Close()
> myConnection2 = Nothing
> End Try
> End Sub
> End Module
> a insert query i use:
> SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing,
commentaar, begin_, einde_, monteur)" & _
> " VALUES (" & MM02_Inv_Stor & ",
'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(),
"dd-MM-yyyy HH:mm:ss") & "', '--')", True)
> a update query i use:
> SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" &
Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
> and finally a select query:
> SQL.ExeQuery("SELECT * FROM mm_telling", False)
> every 5 seconds i write abouth 120 update and insert queries and abouth
150 select queries.
> the most tables in this database are a few rows (abouth 10 to 20)
> The server is MSSQL 2000 with SP 3a
> I hope there is a solution for this problem
> Martin IJzerman
>
>|||Uri
What do you mean with, 'a certain amount of blocking is normal and unavoidab
le.', i hope it is not true.
In my application blocking is not acceptable. before i have written a applic
ation with mysql as database-server and after 2.5 years 24 hours 7 days a we
ek, there are never blocking problems. The policy by the factory i work for
is using MSSQLserver 2000,
and there is no possibility to change to a other db-server.
I shall try to cancel the query in state of rolling them back.
Thanks, Martin IJzerman|||Martin
Unfortunatly it is true.
Blocking occurs when one connection to SQL Server locks one or more records,
and a second connection to SQL Server requires a conflicting lock type on
the record or records locked by the first connection. This causes the second
connection to wait until the first connection releases its locks. By
default, a connection will wait an unlimited amount of time for the blocking
lock to go away.
To help identify blocking in your databases, Microsoft has two separate
stored procedures listed on their website (one each for SQL Server 7.0 and
2000) you can use to help identify blocking problems on your SQL Servers. On
these pages are scripts you can use to create stored procedures that you can
run anytime to help you identify blocking issues.
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
"MartinY" <anonymous@.discussions.microsoft.com> wrote in message
news:D24D7BA8-48DC-4FFC-828F-7231E753EC08@.microsoft.com...
> Uri
> What do you mean with, 'a certain amount of blocking is normal and
unavoidable.', i hope it is not true.
> In my application blocking is not acceptable. before i have written a
application with mysql as database-server and after 2.5 years 24 hours 7
days a week, there are never blocking problems. The policy by the factory i
work for is using MSSQLserver 2000, and there is no possibility to change to
a other db-server.
> I shall try to cancel the query in state of rolling them back.
> Thanks, Martin IJzerman
>|||Ury,
I have tried 'How to Monitor SQL Server 2000 Blocking (Q271509)' script and
in the logging file there is no blocking detected, and also in the profiler
trace there are no special things to see. There is after 10 to 14 hours some
blocking with is not detec
ted.
Martin IJzerman.
