Timeout error when inserting and reading from same table in sql server and .net

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/microsoft.public.dotnet.framework.adonet/browse_thread/thread/60921b45f9dc00e4/0494ca46ee4147bb?lnk=st&q=sql+server+.net+timeout+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=servername;Database=ProjectServer;UID=sa;pwd=pwd"
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

Omar
Mon Jan 22 14:59:17 CST 2007

Hope this helps whoever encounters a similar problem. If all you need
is to just select a record for which you are sure the column that you
want will not be changed(like in my case), you can use the (nolock)
function.

"select column_A , column_B from myTable(nolock) where blah='bleh'"

Enjoy, and do not be frustrated that you have been searching this for
hours :)

Regards,
Omar

Omar wrote:
> 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/microsoft.public.dotnet.framework.adonet/browse_thread/thread/60921b45f9dc00e4/0494ca46ee4147bb?lnk=st&q=sql+server+.net+timeout+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=servername;Database=ProjectServer;UID=sa;pwd=pwd"
> 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