Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 892
  • Last Modified:

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
0
shahjagat
Asked:
shahjagat
  • 6
  • 6
  • 3
  • +1
2 Solutions
 
Mohit VijayCommented:
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.
0
 
shahjagatAuthor Commented:
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
0
 
Mohit VijayCommented:
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.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Mohit VijayCommented:
or can you post your code, code behind and SQL code.
0
 
joriszwaenepoelCommented:
Maybe your query just takes too long?  Longer then the CommandTimeout for your SqlCommand?
0
 
Mohit VijayCommented:
CommandTimeout=0 value will allow your code to wait for infinite time for response. but it will slower your application performance and you will never able to look into query performace/optimization issues, because as per human nature, if things are working fine, he will never get into the problems those are hidden.
0
 
shahjagatAuthor Commented:
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
0
 
Mohit VijayCommented:
change one line as

mycommand = New SqlCommand("select Trip_ID from schedules WITH (NOLOCK) where leg_lnk =" + strAddResrvReturnVal, cls_Global.gSqlConnObj)
0
 
shahjagatAuthor Commented:
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
0
 
kris_perCommented:

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...
0
 
kris_perCommented:

Also from the code it looks...the connection object is global...if there are any chances that two pages (or multiple users if conn object is that much global) use the same global connection object concurrently, then this kind of issues can happen...So I would suggest you use local variables to store the conn object like:

Remove this line => cls_Global.gSqlConnObj = MyDBConnection.OpenSQLConnection

instead use
this line => Dim conn As SqlConnection = MyDBConnection.OpenSQLConnection

then when closing, close the local conn variable/object...
MyDBConnection.CloseSQLConnection(conn)

You may need to do this not only for this page...but other pages where global connection variable is used...generally it is recommened to open connection, use it and immediately close it within the scope of one operation...

Hope this helps.

0
 
Mohit VijayCommented:
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.
0
 
shahjagatAuthor Commented:
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
0
 
kris_perCommented:
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.
0
 
shahjagatAuthor Commented:
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
0
 
shahjagatAuthor Commented:
Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now