The timeout period elapsed prior to completion of the operation or the server is not responding even with connection timeout = 0

Question

I have a query that is having to do a open row set to a remote Server (by satellite) I have set my time out to 0 "AND YES i know not to do that" I run my query in SSMS and it can take take any where from 3min to 1.5 hrs to return. "Yes I know" but it is what it is. I have done all the sql back end optimize... execution plan..... so it is what it is.

So I am running one query that in SSMS takes 5 min. No problem but when I run it in my app to populate my DS I get "the timeout period......." even with my connection timeout set to 0

string connString = ("server = " + _SourceVal + "; user id = myid;
 password = mypw; database = mydb;Connection Timeout=0");

so put a time check in my code and it is only running 34-35 seconds before it drops in to my exception

Console.WriteLine(DateTime.Now + "Start");

        try
        {
            conn.Open();
            da.Fill(ds);
            conn.Close();

        }
        catch (Exception e)
        {
            Console.WriteLine(DateTime.Now + "end");
            MessageBox.Show("error with the findingMisMatch " + e.Message);
        }

        return ds;

RESULTS

11/2/2016 2:34:00 PMStart The thread '' (0x1944) has exited with code 0 (0x0). A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll 11/2/2016 2:34:35 PMend

Vasanth Vasanth · Accepted Answer · 2016-11-02T19:39:25

You would have to increase the CommandTimeout, by defaults its 30 seconds. So in your case you can set to 0 for unlimited.

Here is a sample from MSDN: [https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.110).aspx]

using System;
using System.Data.SqlClient;
/// 
public class A {
   /// 
   public static void Main() {
      string connectionString = "";
      // Wait for 5 second delay in the command
      string queryString = "waitfor delay '00:00:05'";
      using (SqlConnection connection = new SqlConnection(connectionString))     {
         connection.Open();
         SqlCommand command = new SqlCommand(queryString, connection);
         // Setting command timeout to 1 second
         command.CommandTimeout = 1;
         try {
            command.ExecuteNonQuery();
         }
         catch (SqlException e) {
            Console.WriteLine("Got expected SqlException due to command     timeout ");
            Console.WriteLine(e);
         }
      }
   }
}

The timeout period elapsed prior to completion of the operation or the server is not responding even with connection timeout = 0

1 Answers