locked
SQL Connection Timeout RRS feed

  • Question

  • I'm in Data Tools and have a script to select data and write to a csv file which works fine, see connection sample below.

    I need to run this on a large remote table and it select takes about 4mins in total to select but after 30 seconds I'm getting a timeout.

    I changed the Connection Manager Timeout to 300, 600 and 0 but it didn't change the time when it errors.

    So I guess this timeout is only the time to connect to server and not runtime?

    How would I make it wait longer for running the job?

    Thanks.

    sample script

     //USE ADO.NET Connection from SSIS Package to get data from table
    SqlConnection myADONETConnection = new SqlConnection();
    myADONETConnection = (SqlConnection)(Dts.Connections["DB_Conn_TEST"].AcquireConnection(Dts.Transaction) as SqlConnection);

    //Execute Stored Procedure and save results in data table
     string query = "EXEC " + StoredProcedureName;
    SqlCommand cmd = new SqlCommand(query, myADONETConnection);
    DataTable d_table = new DataTable();
    d_table.Load(cmd.ExecuteReader());
    myADONETConnection.Close();

    Monday, August 14, 2017 2:18 PM

Answers

  • Connect timeout is the timeout waiting for the server to respond to the initial connection request, not for executing commands. To set the timeout for executing a command you need to set the CommandTimeout property of the command object.

    var cmd = new SqlCommand(...);
    cmd.CommandTimeout = 4 * 60; //240 seconds

    Michael Taylor
    http://www.michaeltaylorp3.net


    • Edited by CoolDadTx Monday, August 14, 2017 4:12 PM Added link
    • Proposed as answer by Kelmen Tuesday, August 15, 2017 2:39 AM
    • Marked as answer by Jay1987 Tuesday, August 15, 2017 1:01 PM
    Monday, August 14, 2017 4:12 PM

All replies

  • This is in the error:

    System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

    Monday, August 14, 2017 2:19 PM
  • Also to add If I look at the string in connection manager: DB_Conn_TEST

    Data Source=JA-DB;Initial Catalog=Test_Jobs;Integrated Security=True;Connect Timeout=300;Application Name=SSIS-Package-{35489926-EB50-4E8B-B87E-753832BB8877}JA-DB.Test_Jobs;

    It has Connect Timeout=300, is the script maybe not picking this up?

    Monday, August 14, 2017 2:35 PM
  • Connect timeout is the timeout waiting for the server to respond to the initial connection request, not for executing commands. To set the timeout for executing a command you need to set the CommandTimeout property of the command object.

    var cmd = new SqlCommand(...);
    cmd.CommandTimeout = 4 * 60; //240 seconds

    Michael Taylor
    http://www.michaeltaylorp3.net


    • Edited by CoolDadTx Monday, August 14, 2017 4:12 PM Added link
    • Proposed as answer by Kelmen Tuesday, August 15, 2017 2:39 AM
    • Marked as answer by Jay1987 Tuesday, August 15, 2017 1:01 PM
    Monday, August 14, 2017 4:12 PM
  • Thanks Michael,

    OK that makes sense I was trying to change the wrong setting, I just tried adding the line but getting an error on:

    SqlConnection myADONETConnection = new SqlConnection();
    myADONETConnection.CommandTimeout = 4 * 60; //240 seconds
    myADONETConnection = (SqlConnection)(Dts.Connections["DB_Conn_TEST"].AcquireConnection(Dts.Transaction) as SqlConnection);

    I'm just going to have a search around to try and figure out the problem but can you see where I have gone wrong?

    Tuesday, August 15, 2017 9:39 AM
  • Arghh sorry Michael I've been a numpty I've just spotted where this was meant to go, after this line:

    cmd = new SqlCommand(query, myADONETConnection);
    cmd.CommandTimeout = 4 * 60; //240 seconds

    Thanks for your help.

    Tuesday, August 15, 2017 1:01 PM