How to set Connection time out and Command timeout in SQL server-System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Hi

If you got the above error when SQL query take more time to execute the statement, In that case you get the below error..

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

How to resolve the above issues:

To resolve the issues you need to set the timeout SQL command execution

strcmd = New SqlCommand(query, con)

strcmd.CommandTimeout = 120

In normally in SQL server two type of time out has been followed

1. SqlCommand.CommandTimeout

2. SqlConnection.ConnectionTimeout

Don’t confuse with the two timeout properties, both are totally different.

Here I will explain the details about the difference..

CommandTimeout :

Sqlcommand.commandtimeout states that sql command time executioni.e how much time (select,update) query  statement can take for its execution.

Command Timeout you can set at the time of executing the query

strcmd = New SqlCommand(query, con) strcmd.CommandTimeout = 120

ConnectionTimeout:

SqlConnection.ConnectionTimeout states that time limit for sql connection i.e how much time connection object can try to open the connection,Connection.Open()

SqlConnection.ConnectionTimeout can set directly at the connection string which we assigned in the web.config file.

<add connectionString="Data source=;database=;uid=g;pwd=; Connection Timeout=120;" name=" " />

I hope you get some idead about connection timeout followed in SQL SERVER

Post you comment and share the articles with your hangout place……