asked on

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Hi,

I have a winforms application.
What might be the reason for this error?
Why is the server not responding. Is this somehting in my code or is it an issue with the server?
I am using sql server 2005 express edition

I am getting the follwoing error:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName)
at System.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso, String transactionName)
at System.Data.SqlClient.SqlConnection.BeginTransaction()
at TransMgrProject.cls_ReservationsLeg.addReservations(Hashtable[]& arryColResrvObjs, String frmName, String typAction)
at TransMgrProject.frm_Reservations.addreservations()
at TransMgrProject.frm_Reservations.btnSave_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Thanks in Advance

Mohit Vijay

No Problem is not related to your winform code. Problem is in between connection with your winform application and SQL Transaction connection.

1. Please verify if your SQL connection is properly responding or not.
2. Please verify if you have specified correct SQL connection string in your code.
3. Please verify if you are executing SQL statements are responding or not.
4. Also check Deadlock on SQL level. run a command sp_who2, if blk_by column has any id, its mean your commands are in deadlock, if its happening use "kill <id>"

Please let me know if you need further clarification.

shahjagat

ASKER

Hi vjSoft,

The connection string is good and the sql connection is not a problem.
The queries are returning values properly.
We do not use stored procedures.
Is there any other way to check?

Thanks

Mohit Vijay

System.Data.SqlClient.SqlException shows that error is on SQL end, some statements are not returning value on time.

I think on frm_Reservations, you have a button called btnSave, that has event btnSave_Click, in this event you are calling a method addreservations.
Please check SQL Statements those are executing in this method.

Mohit Vijay

or can you post your code, code behind and SQL code.

joriszwaenepoel

Maybe your query just takes too long? Longer then the CommandTimeout for your SqlCommand?

ASKER CERTIFIED SOLUTION

Mohit Vijay

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shahjagat

ASKER

Hi vjsoft, this is the function that you saw in the error.
But the problem might not be here alone. Some times it is on another page.

My question is why does the command time out? Why does the command take that long time to execute teh query.?

Function addreservations() As String
Dim strAddResrvReturnVal As String
Dim returnval As String = Nothing
cls_Global.gSqlConnObj = MyDBConnection.OpenSQLConnection
Dim mycommand As SqlCommand
Dim dr As SqlDataReader
Dim ds As New DataSet
Dim teststring As String = Nothing

strAddResrvReturnVal = myClsReservation.addReservations(arryTabsData, tabType, "ADD")
If tabCount > 1 Then
mycommand = New SqlCommand("select Trip_ID from schedules where leg_lnk =" + strAddResrvReturnVal, cls_Global.gSqlConnObj)
dr = mycommand.ExecuteReader()
While dr.Read()
If (returnval Is Nothing) Then
returnval = dr.GetString(0)
Else
returnval = returnval + "," + dr.GetString(0)
End If
strAddResrvReturnVal = returnval
End While
End If

If strAddResrvReturnVal <> "ERROR" Then
'MessageBox.Show("Reservation(s) saved successfully! Your confirmation number is " + strAddResrvReturnVal + " Do you wish to add more ?", "Saved successfully", MessageBoxButtons.OK, MessageBoxIcon.Information)
Dim ans As String
ans = MessageBox.Show("Reservation(s) saved successfully! Your confirmation number is " + strAddResrvReturnVal + " Do you wish to add more ?","Saved successfully", MessageBoxButtons.YesNo, MessageBoxIcon.Information)
If ans = MsgBoxResult.Yes Then
clearData()
Else
Me.Close()
End If

Else
MessageBox.Show("Transaction was not sucessfully added to the system", "Error message", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If

mycommand = Nothing

MyDBConnection.CloseSQLConnection(cls_Global.gSqlConnObj)
Return strAddResrvReturnVal
End Function

Thanks

Mohit Vijay

change one line as

mycommand = New SqlCommand("select Trip_ID from schedules WITH (NOLOCK) where leg_lnk =" + strAddResrvReturnVal, cls_Global.gSqlConnObj)

shahjagat

ASKER

Hi vjsoft,
Our application we are not using stored proccedures so, we have more queries.
Do i have to use this with(nolock) in every query?
What makes the command to time out?

The reason is i am getting this error randomly on different pages.

Ravi

kris_per

You need to dispose the SqlDataReader after using it...Though I see a close connection call in your code...better to dr.Dispose() the reader...as it is the recommended practice....see if this would make any difference...

SOLUTION

kris_per

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mohit Vijay

Yes with every select query you should use WITH (NOLOCK). Its not matter if you are using stored procedure or not. But preventing deadlocks and table locks you should use WITH (NOLOCK) when you are trying to access data from table.

CommandTimeout property is used to specify time (in seconds), It tells that your application should wait for response from SQL query for given seconds .
If you set it to 0, it will wait for infinite time.

shahjagat

ASKER

vjsoft,
NOLOCK did not make any difference in this. I am still getting this issue.

kris_per:
Would a global connection be sucha a big issue?
Or would it be because of using sql server 2005 express edition

kris_per

Using global variable requires bit-more extra care. If it is not used properly, it can cause issues....as I mentioned above, first you can try disposing the reader in your code and then try using local variables for connections.

shahjagat

ASKER

Looks like this issue is resolved as i am not getting this error.

Changes i made:

Couple of connections were closed out of scope. and i set command time out values for all of them

shahjagat

ASKER

Thanks