Timeout expired when running an SP more than 30 seconds

Question

I have an sp that runs for 45 sec- 1 min but it gives me the error message Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I have add the Connect Timeout=120 to the connection string but that didnt help. I've also change the IIS application pool to timeout after 2 mins.

<add name="Oconnection" connectionString="Data Source=servername;Initial Catalog=dmName; User ID=username; Password=pw; Connect Timeout=120" />

This is my cs file:

  string Oconnection=ConfigurationManager.ConnectionStrings["Oconnection"].ConnectionString;



 public DataSet CreateTable()
    {
        DataSet dsCreateTable;
        dsCreateTable = SqlHelper.ExecuteDataset(Oconnection, CommandType.StoredProcedure, "usp_CreateTables");
        return dsCreateTable;
    }

Do I need to add the timeout in the cs file as well?

That's the timeout for establishing a connection (hence the name). The command timeout can only be set in [code](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?redirectedfrom=MSDN&view=netframework-4.7.2#System_Data_SqlClient_SqlCommand_CommandTimeout) — , Jan 10 '19 at 14:54
Related: [What is “Connect Timeout” in sql server connection string?](https://stackoverflow.com/questions/20142746/what-is-connect-timeout-in-sql-server-connection-string) — Hans Keﬆing, Jan 10 '19 at 15:23

mortb · Accepted Answer · 2019-05-09T10:22:05.290

7

Connect Timeout is the time limit for connecting to the sql server.

What you want is CommandTimeout which is the timeout for running a command (query, stored procedure etc)

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=netframework-4.7.2

You need to set the CommandTimeout proptery on the SqlCommand object, it is not possible to set in connection string.

You can use it in your code like this:

public DataSet CreateTable()
{
    using(var conn = new SqlConnection(Oconnection))
    {
       conn.Open();
       using(var command = new SqlCommand())
       {
          command.Connection = conn;
          command.CommandTimeout = 120; // higher if needed
          command.CommandType = CommandType.StoredProcedure;
          command.CommandText = "usp_CreateTables";
          var da = new SqlDataAdapter();
          da.SelectCommand = command;
          var ds = new DataSet();
          da.Fill(ds);

          return ds;
      }
   }
}

edited May 09 '19 at 10:22

answered Jan 10 '19 at 14:52

mortb

9,361
3
26
44

Thanks, how can I implement it in my current code? (newbie here) – Charles Xavier Jan 10 '19 at 14:55
1

Your only option is to change the code in `ExecuteDataset`. Somewhere in that code you have a `SqlCommand` object. That object has the `CommandTimeout` property. – mortb Jan 10 '19 at 14:57
I got this error at the command.Connection line: CS0029: Cannot implicitly convert type 'string' to 'System.Data.SqlClient.SqlConnection' – Charles Xavier Jan 10 '19 at 15:05
1

I guess your variable Oconnection just contains the connection string? I editied the code to create a new connection. – mortb Jan 10 '19 at 15:10
1

Take a look at the answers here for more examples: https://stackoverflow.com/questions/13402003/how-to-populate-a-datatable-from-a-stored-procedure – mortb Jan 10 '19 at 15:13
I tried it but still the same error. Also SqlConnection was misspelt. – Charles Xavier Jan 10 '19 at 15:18
same error? Which is Cs2009 or that the stored procedure times out, or...? – mortb Jan 10 '19 at 15:22
1

Now I wrote the code for you. Of course I have not tested it since I do not have the database. – mortb Jan 10 '19 at 15:45
1

No problem. I wish you the best in learning more about c# / .NET. – mortb Jan 11 '19 at 09:19

Timeout expired when running an SP more than 30 seconds

1 Answers1