Solved

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

Posted on 2010-08-25
16
840 Views
Last Modified: 2012-05-10
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
Comment
Question by:shahjagat
  • 6
  • 6
  • 3
  • +1
16 Comments
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33527907
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
 

Author Comment

by:shahjagat
ID: 33528185
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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33528228
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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33528229
or can you post your code, code behind and SQL code.
0
 
LVL 10

Expert Comment

by:joriszwaenepoel
ID: 33529441
Maybe your query just takes too long?  Longer then the CommandTimeout for your SqlCommand?
0
 
LVL 8

Accepted Solution

by:
Mohit Vijay earned 250 total points
ID: 33529466
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
 

Author Comment

by:shahjagat
ID: 33532210
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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33532241
change one line as

mycommand = New SqlCommand("select Trip_ID from schedules WITH (NOLOCK) where leg_lnk =" + strAddResrvReturnVal, cls_Global.gSqlConnObj)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:shahjagat
ID: 33533054
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
 
LVL 16

Expert Comment

by:kris_per
ID: 33533307

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
 
LVL 16

Assisted Solution

by:kris_per
kris_per earned 250 total points
ID: 33533409

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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33534132
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
 

Author Comment

by:shahjagat
ID: 33559875
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
 
LVL 16

Expert Comment

by:kris_per
ID: 33570680
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
 

Author Comment

by:shahjagat
ID: 33633144
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
 

Author Closing Comment

by:shahjagat
ID: 33633149
Thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now