Timeout when reading and updating / inserting to same table

[Omar]

Hi All,
I have a headache with SQL Server and .net application which I hope you
can help with.

I have this trigger on my table 'tester' in sql server 2000. It calls
the external program exetest

CREATE TRIGGER testTrig ON [dbo].[tester]
FOR INSERT, UPDATE
AS
exec master..xp_cmdShell 'C:\ExeTest.exe'

I am calling the ExeTest.exe program which is supposed to read from the
same table again and just display first.

I am having a similar problem as this guy below( i.e updating and
reading the same table. But there is no solution)

http://groups.google.com/group/micr...expired+sqldatareader&rnum=1#0494ca46ee4147bb

Is there a way that I can achieve this? Even if it is long, I do not
mind as long as there is no timeout. I have set the timeout to 5
minutes plus, and still got a timeout error. It seems the table is
locked and cannot be selected from???

If you still reading, here is my code in the ExeTest.exe

Sub Main()

Dim sqlstring As String = "select * from tester"
'Dim connString1 As String = "Data
Source=jktgrhxsapps1;Database=ProjectServer;UID=sa;pwd=ProjectServer"
Dim connString1 As String = "Data
Source=localhost;Database=ProjectServer;Integrated Security=true"
Dim conn As New SqlConnection
conn = New SqlConnection(connString1)
Try
conn.Open()
Dim cmd As New SqlCommand(sqlstring, conn)
cmd.CommandTimeout = 40
Dim listA As SqlDataReader = cmd.ExecuteReader()

While listA.Read
Console.WriteLine(listA(1))
End While
'End If
listA.Close()
conn.Close()
Catch ex As Exception
Console.WriteLine("error : " + ex.Message)
End Try

End Sub

Regards,
Omar

 

[Miha Markic [MVP C#]]

Hi Omar,

I guess you are experiencing database locking mechanism that prevents
concurrent actions on same row.
Thus you might try with less restrictive explicit transaction (see
SqlConnection.BeginTransaction) or delayed operation (so the current
transaction finishes) by test.exe.

 

[Petar Repac]

Omar,

it seems to me like a very bad design to spawn an external program in
update trigger and that program reads the same table.

What would happen if you do an update on 1000 rows ?

I agree with Miha. Your C:\ExeTest.exe cannot read the table
because your transaction did not commit yet. The 2 programs are in death
lock and you get timeout.

You should consider to implement your logic in stored procedure and get
rid of the C:\ExeTest.exe.

Regards, Petar Repac