By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,563 Members | 1,205 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,563 IT Pros & Developers. It's quick & easy.

all Providers ignore time out property

P: n/a
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
Jul 21 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
"rsherman" <an*******@discussions.microsoft.com> wrote in message
news:01****************************@phx.gbl...
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

Jul 21 '05 #2

P: n/a
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
-----Original Message-----
Set the command Timeout. The connection timeout only affects the time framethat it takes the app to do the handshake with the server so it doesn't haveany bearing on the execution time of a command.

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

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

Anyway, the Command.Timeout should fix it for you. Let me know if it doesn.t
Bill
"rsherman" <an*******@discussions.microsoft.com> wrote in messagenews:01****************************@phx.gbl...
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

.

Jul 21 '05 #3

P: n/a
I'm not sure what you are asking me...both Connection and Command objects
have a timeout property. They timeout for entirely different reasons.
<an*******@discussions.microsoft.com> wrote in message
news:03****************************@phx.gbl...
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
-----Original Message-----
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
"rsherman" <an*******@discussions.microsoft.com> wrote

in message
news:01****************************@phx.gbl...
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

.

Jul 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.