How to Catch SQLserver Timeout Exceptions

How to catch SQLServer timeout exceptions

To check for a timeout, I believe you check the value of ex.Number. If it is -2, then you have a timeout situation.

-2 is the error code for timeout, returned from DBNETLIB, the MDAC driver for SQL Server. This can be seen by downloading Reflector, and looking under System.Data.SqlClient.TdsEnums for TIMEOUT_EXPIRED.

Your code would read:

if (ex.Number == -2)
{
     //handle timeout
}

Code to demonstrate failure:

try
{
    SqlConnection sql = new SqlConnection(@"Network Library=DBMSSOCN;Data Source=YourServer,1433;Initial Catalog=YourDB;Integrated Security=SSPI;");
    sql.Open();

    SqlCommand cmd = sql.CreateCommand();
    cmd.CommandText = "DECLARE @i int WHILE EXISTS (SELECT 1 from sysobjects) BEGIN SELECT @i = 1 END";
    cmd.ExecuteNonQuery(); // This line will timeout.

    cmd.Dispose();
    sql.Close();
}
catch (SqlException ex)
{
    if (ex.Number == -2) {
        Console.WriteLine ("Timeout occurred");
    }
}

Distinguish between SQL Server Timeout Exceptions and SqlCommand Timeout Exceptions in C#

Yes, you can. Note connection timeout occurs when your DBMS doesn't respond to your app call while a command timout means the DBMS reponded. In fact, it sends a "fail" in order to your drive throws an exception.
While in the command timout it received a explicit error code from the server in the connection timeout your app cannot know what happened (maybe someone pulled the network cable, maybe the server got shutdown) but the bowels of the driver/framework will tell you what happened using the number property .

The code below will look for the exception number property and check if it's a command timeout.

try
{
   //your .net C# code here 
}    
catch (SqlException ex)
{
    if (ex.Number == -2 && (Regex.IsMatch(sqlEx.Message, "[tT]ime\-{0,1}out")) {
        Console.WriteLine ("Command Timeout occurred")
    };
}

Also you can parse the exception text to get when it's another kind of command error, like a FK violation for example. Just do a regex matching "timeout" word.

references: MSDN

MSDN Forum

How to catch SQLServer timeout exceptions

Getting SQL Server timeout exception even with command timeout set to 0

First, connection timeout and command timeout are not the same thing. Make sure you understand the difference and are using them correctly.

Second, if this is from a web page, you also need to consider timeout values relating to the web server, etc.

Third, to verify that it is in fact a timeout issue, execute your index statement in SSMS and find out how long it takes. Since the actual indexing takes place on the SQL server no matter where it is called from, the indexing time should be roughly equal whether running from SSMS or your application.

How to use try catch error for sql query timeout?

try-catch will catch the exception as soon as it occurs, but don't expect to catch a timeout exception before the timeout occurs. For SqlCommand, the default timeout is 30 seconds, but you can change it by setting the CommandTimeout property:

try
{ 
    var command = new SqlCommand("...");
    command.CommandTimeout = 5; // 5 seconds
    command.ExecuteNonQuery();
}
catch(SqlException ex)
{
    // handle the exception...
}

SQL Server - create timeout exception in function

Try this. You can pass whatever value you want and it'll loop through until it reaches 0. Too high of a number throws an arithmetic error.

Function:

CREATE FUNCTION dbo.timeoutFunction(@P1 int)
RETURNS @table TABLE 
(
    Id int
)
AS

BEGIN

WHILE @P1 > 0 
BEGIN  
      SET @P1 -= 1
      INSERT INTO @table
      SELECT @P1
END
RETURN
END  

Call:

SELECT * FROM timeoutfunction(1000000)

Filtering SqlServer Command Timeout Exception for ExecReader

Have you tried having a look here: How to catch SQLServer timeout exceptions ?

---

Related Topics