About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Thursday, August 28, 2008

Timeout Errors Prevention

After a while, you may end up getting complaints or request dealing with timeout issues. I’m going to use this post to help collect some ways to increase the timeout seconds and prevent errors from happening.

*Note: Even though you increase the timeout seconds, timeouts might still occur but with a longer delay then before. This might make your customers even more upset because of the longer wait for the error to display.

  • In code, increase the sqlCommand timeout. Ex:
     
  • Dim myCommand As New SqlCommand("[dbo].[spSetUserPreferences]", myConnection)

    myCommand.CommandType = CommandType.StoredProcedure
    'change default time out setting
    myCommand.CommandTimeout = 120

  • In code, increase the connection’s string timeout by appending “Connection Timeout=” to it. Ex:

    Data Source=mydatabase;Initial Catalog=Match;Persist Security Info=True;User ID=User;Password=password;Connection Timeout=120
  • On SQL-Server 2005, In management studio’s Tools > Option > Designers Increase the “Transaction time-out after:” even if  “Override connection string time-out value for table designer updates” checked/unchecked. 
  • Make (non-dynamic) stored procedures instead of using inline sql statements within the code. This will also allow for easy fixes instead of having to recompile and deploy for future changes.

 

If you have any other suggestions, please contact me so I can add them to the list.