Setting ConnectionTimeout for OdbcConnection (System.Data.Odbc) in c# does not work

Question

I have a .net 4.0 c# application which needs an odbc connection to mssql 2008. The connection usually works perfect. But when I run a query which usually runs for some minutes (and no, at this point in the project I can't optimise the query runtime) I'll get a timeout after 30 seconds:

ERROR [HYT00] [Microsoft][SQL Server Native Client 10.0]Query timeout expired

I have already tried to change the driver (sqlsrv32.dll, sqlncli10.dll, sqlncli11.dll) for the odbc connection but I get always a similar error.

This is the dummy code I have built to simulate the problem:

using (
    OdbcConnection conn =
        new OdbcConnection("DSN=d3FA;uid=sa;pwd=1234;MARS_Connection=yes;Connection Timeout=37;"))
{
    conn.ConnectionTimeout = 37;
    conn.Open();

    OdbcCommand myCommand = new OdbcCommand("WAITFOR DELAY '00:00:45'", conn);
    myCommand.ExecuteNonQuery();
}

As you can see I have set the Connection Timeout in the connection string and I also set the property of the connection. But it seems that this information (37 seconds) is just ignored from the query execution. The timeout still occurs after 30 seconds.

From Microsoft I have the information that the Windows ODBC API offers the SQLSetStmtAttr-Function which would allow setting the SQL_ATTR_QUERY_TIMEOUT attribute. Perhaps setting this attribute would solve my problem - but I couldn't find any solution how I could set this value through .net.

Conclusion: Has anybody an idea how to increase the timeout value?

Answer 1

Use the CommandTimeout property of your OdbcCommand object like

myCommand.CommandTimeout=37;