all Providers ignore time out property

[rsherman]

I am running a windows app. with a stored procedure and
the stored proc runs up to 90 seconds, the time out in
the connection string is set for 90 seconds. It seems
that the time out setting is ignored no matter what I set
it to. I have tried all different objects e.g.
datatables,etc... and all providers
(OLDEDB,ODBC,SLQCLIENT) If the SP runs under 30 seconds
the app works, else it returns:

System.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the
operation or the server

This is all the code!!

Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Imports System.Diagnostics
Imports System.Configuration
Imports System.Data.Odbc

Public Class Form1

Inherits System.Windows.Forms.Form

Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load

Dim sConn As String

sConn = "user id=sa;pwd=pass;database=sample;
server=sample; Connect Timeout=90;"



Dim sStart As String = txtStartDate.Text.Trim

Dim sEnd As String = txtEndDate.Text.Trim

Dim sStore As String = txtStore.Text.Trim

Dim conn As New SqlConnection(sConn)

Dim cmd As New SqlCommand("gp_LasikCommissions_1", conn)

cmd.CommandType = CommandType.StoredProcedure

conn.Open()


Dim Param As New SqlParameter("@StartDate",
SqlDbType.DateTime)

Param.Direction = ParameterDirection.Input

Param.Value = sStart

cmd.Parameters.Add(Param)

Dim Param1 As New SqlParameter("@EndDate",
SqlDbType.DateTime)

Param1.Direction = ParameterDirection.Input

Param1.Value = sEnd

cmd.Parameters.Add(Param1)

Dim Param2 As New SqlParameter("@Store_No",
SqlDbType.Char)

Param2.Direction = ParameterDirection.Input

Param2.Value = sStore

cmd.Parameters.Add(Param2)

Dim myReader As SqlDataReader

Try

myReader = cmd.ExecuteReader()

Catch ex As Exception

Console.WriteLine(ex.ToString)

End Try



Try

While myReader.Read()

Console.WriteLine((myReader(0).ToString & ", " & myReader
(1).ToString & ", " & myReader(2).ToString))

End While

Catch ex As NullReferenceException

Console.WriteLine(ex.ToString)

Finally

' always call Close when done reading.

If Not myReader Is Nothing Then

myReader.Close()

End If

' always call Close when done reading.

conn.Close()

End Try

End Sub

End Class

 

[William Ryan]

Set the command Timeout. The connection timeout only affects the time frame
that it takes the app to do the handshake with the server so it doesn't have
any bearing on the execution time of a command.

The command has a timeout property as well and adjusting it upward should do
the trick.

As an aside, I'd recommend taking a look at why my Proc takes 90 seconds to
run. Of course it depends on the type of app, but if there's user
interatction, that's a long time to run...may want to spin off a different
thread.... Just for giggles, run that proc through the index tuning wizard
and see what it comes up with, even though you can adjust the timeout
period, if you can shrink the time frame down, it may be worth adding some
indices (although I'm not implying that slapping on indexes is always the
best way to solve performance problems, b/c you have to maintain them and
they affect the speed of inserts and all.)

Anyway, the Command.Timeout should fix it for you. Let me know if it doesn.t

Bill

 

[Guest]

How about connection objects?


Public Function GetDataTableODBC(ByVal sSQL As
String, ByVal sConnString As String) As DataTable
Dim dtGeneric As New DataTable

Dim oDataSet As New DataSet 'create a new
dataset...
oDataSet.Clear()
Dim conn As New OdbcConnection(sConnString) 'pass
connString to conn obj...

conn.ConnectionTimeout = 90

Dim objDataAdapter As New OdbcDataAdapter(sSQL,
conn)
Try

objDataAdapter.Fill
(oDataSet, "Generic") 'fill dataSet with data...

Catch ex As OdbcException
MsgBox(ex.ToString, MsgBoxStyle.Critical)
Exit Try
End Try


dtGeneric = oDataSet.Tables("Generic")
Return dtGeneric

End Function

 

[William Ryan]

I'm not sure what you are asking me...both Connection and Command objects
have a timeout property. They timeout for entirely different reasons.