"Timeout expired. The timeout period elapsed prior to

Question

"Timeout expired. The timeout period elapsed prior to

anurag.sharma

SSChasing Mays

Points: 645
More actions
May 9, 2009 at 3:40 am

#72221

Hello Guys,
I am experiencing following SQL error frequently on production server 2000 but we are unable to reproduce it on testing server.
"Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding."
0 " .Net SQL Client Data Provider ConnectionRead (WrapperRead())."
As there are no additional information in the Errors collection, we are stuck here what will be the probable reasons for occurrence of error. We have searched on net also but not found any satisfactory reasons.
I have used the below Mentioned code
i have also set CommandTimeout to 600 in below code
Public Function fExecuteQueryForDataTable(ByVal sSql As String, ByRef objDataTable As Object) As Boolean
Dim objConnection As SqlConnection
Dim objDataAdapter As SqlClient.SqlDataAdapter
Try
If Not (gstrDBType = "ORACLE" Or gstrDBType = "SQL") Then
gstrErrorDesc = "This Application Supports ORACLE And SQL."
Exit Function
End If
objConnection = New SqlClient.SqlConnection(gstrADONETConnString)
objConnection.Open()
If objConnection.State <> ConnectionState.Open Then
gstrErrorDesc = LoadResString(2032)
Exit Function
End If
objDataAdapter = New SqlDataAdapter(sSql, objConnection)
objDataAdapter.SelectCommand.CommandTimeout = 0
objDataTable = New DataTable
objDataAdapter.Fill(objDataTable)
fExecuteQueryForDataTable = True
Catch ex As Exception
gstrErrorDesc = ex.Message
fExecuteQueryForDataTable = False
Finally
If objConnection.State <> ConnectionState.Closed Then
If Not IsNothing(objDataAdapter) Then
objDataAdapter.Dispose()
End If
objConnection.Close()
objConnection.Dispose()
End If
objDataAdapter = Nothing
objConnection = Nothing
End Try
End Function
Pls help us by providing probable solutions to this problem.
Thanks in advance.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

How long does it take until you get the error? If it takes about 10 minutes you have to set a higher command-timeout (or zero for no timeout).

If the execution time is too long you should post the statement and some sample data and (if possible) the execution plan.

Greets

Flo

anurag.sharma SSChasing Mays Points: 645 More actions · Answer 2

Some times it shows error with in a second for small data, and some time it works fine for large data.

Like fist we generate the report using the above code it shows error and then we again press the generate button then it works fine.

Same code faiils Frequently i did not find the reason for it.It works fine on our testings servers but on production server it shows error.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Hi

You wrote "Report". Do you get the error in your report application? SSRS?

Did you try to execute the query in SSMS?

Greets

Flo

anurag.sharma SSChasing Mays Points: 645 More actions · Answer 4

my meaning for Report is of my ASP.NET Web Application through which above code is called to generate the report no SSRS is Usaed.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

One dollar for every ";" I wrote (I'm usually C# coder) ... *sigh*

Could you please surround your command execution with the following code. The code needs to import "System.IO". I marked the position for your code. The CATCH-block will create a detailed error output and writes to the "Output" window. Can you please post the result here?

Try

'''''''''''''''''''''''''''''''''

' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

' ADD YOUR COMMAND EXECUTION HERE

' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

'''''''''''''''''''''''''''''''''

Catch ex As Exception

Dim sw As New StringWriter()

sw.WriteLine()

sw.WriteLine("####################################################")

sw.WriteLine("# -> EXCEPTION LOGGING")

sw.WriteLine()

sw.WriteLine("---------------------")

sw.WriteLine("STACK TRACE:")

sw.WriteLine(ex.StackTrace)

sw.WriteLine()

While Not ex Is Nothing

sw.WriteLine("===========================")

sw.WriteLine("EXCEPTION")

sw.WriteLine("Type: {0}", ex.GetType())

sw.WriteLine("Message: {0}", ex.Message)

sw.WriteLine()

If TypeOf ex Is SqlException Then

Dim sqlEx As SqlException = CType(ex, SqlException)

sw.WriteLine("Number: {0}", sqlEx.Number)

sw.WriteLine("Procedure: {0}", If(sqlEx.Procedure, ""))

sw.WriteLine("State: {0}", sqlEx.State)

For Each e As SqlError In sqlEx.Errors

sw.WriteLine()

sw.WriteLine("----------------------")

sw.WriteLine("SQL ERROR")

sw.WriteLine("Message: {0}", e.Message)

sw.WriteLine("Number: {0}", e.Number)

sw.WriteLine("Procedure: {0}", If(e.Procedure, ""))

sw.WriteLine("State: {0}", e.State)

Next

End If

sw.WriteLine()

ex = ex.InnerException

End While

sw.WriteLine("# <- EXCEPTION LOGGING")

sw.WriteLine("####################################################")

System.Diagnostics.Debug.WriteLine(sw.GetStringBuilder().ToString())

End Try

Thanks

Flo