SqlException: Timeout expired

[Stephen Noronha]

Hi,
I have an unusual error,

"System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding......and so on "
I have never seen such an error.

Yes, i do agree that the SP takes anywhere b/w 2-5 min to return data and i
have no "connection timeout parameter" set in my web.config
the thing which baffles me is that this exception is thrown at about 1 min
range

please advice,
Stephen

 

[Landley]

Is this not a command timeout opposed to a connection timeout?

There is a timeout on the command object that you can set.

 

[Stephen Noronha]

Hi Landley,

here is what I am doing?
Private function GetRecords(ByVal startDate As Date, ByVal endDate As Date,
ByVal Code1 As String, ByVal Code2 As String) As DataTable
Dim cmd As New SqlCommand("stp_GetRecords", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim da As New SqlDataAdapter(cmd)
Dim dt As New DataTable
Try
Dim pms As SqlParameterCollection = cmd.Parameters
pms.Add("@begdate", SqlDbType.SmallDateTime).Value = startDate
pms.Add("@enddate", SqlDbType.SmallDateTime).Value = endDate
pms.Add("@code1", SqlDbType.VarChar, 6).Value = Code1
pms.Add("@code2", SqlDbType.VarChar, 6).Value = Code2

da.Fill(dt) <---- Fails here

If dt.Rows.Count > 0 Then
GetRecords = dt

Else
GetRecords = Nothing

End If

Catch ex As Exception
'Throw New Exception
lblError.Text = ex.ToString

End Try
Return GetRecords

End Function

It gives me a timeout error while its filling the datatable,
Please advice,
Stephen

 

[Pavan]

Stephen,
I think your SP is timing out because of the remote conection
timeout set on your database server. Try the following steps in your
SQLServer.

1)In Object Explorer, right-click a server and select Properties.

2)Click the Connections node.

3)Under Remote server connections, in the Remote query timeout box,
type or select a value from 0 through 2,147,483,647 to set the maximum
number seconds for SQL Server to wait before timing out.

Hope this helps......

Regards,
Pavan.

 

[Bruce Barker]

SqlCommand has a 1 minute timeout by default. set it higher. you will then
have to bump up the page timeout. though 2-3 minutes is way too long, the
broser/proxy may give up. certainty the user will.

-- bruce (sqlwork.com)