Command Timeout while running a migration query

[GoogleCodeExporter]

What steps will reproduce the problem?
1. 'Big' table aprox. 2 million records in sql server
2. Update query on one of the tables fields
3. 30s of your time
What is the expected output? 
Expected output: everything but timeout related exceptions
What do you see instead?
A timeout related exception
What version of the product are you using? 
0.7
On what operating system?
WinXp
With what .NET implementation/version?
2.0
What database and version are you seeing this issue on?
SqlServer 2005
Please provide any additional information below.
It seems an IDbCommand has a default timeout value of 30 seconds and this
proves to be too little for some of the queries I attempt to run and
unfortunately this timeout cannot be configured in the connection string or
any other place than a newly created IDbCommand object.

I managed to get around it by writing my own ExecuteQuery method like this:
public static int ExecuteNonQuery(ITransformationProvider Database, string
sql, int timeout)
        {
            System.Data.IDbCommand cmd =
Database.GetCommand().Connection.CreateCommand();
            cmd.CommandTimeout = timeout;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = sql;
            cmd.Transaction = Database.GetCommand().Transaction;
            return cmd.ExecuteNonQuery();
        }
 Maybe you can provide a global setting for such a timeout or reuse the
timeout set in the connection string?

Regards,
Jasper

Original issue reported on code.google.com by jaspervo...@gmail.com on 30 Jul 2008 at 1:21

[GoogleCodeExporter]

I have set the CommandTimeout to 90 seconds instead of the default of 30 
seconds. 
This should cover most cases of large migrations.

resolved in r113

Original comment by dko...@gmail.com on 6 Aug 2008 at 1:06

• Changed state: Fixed

[GoogleCodeExporter]

Perhaps this could be addressed like so:

[Migration(42, Timeout = 600)]
public class MigrationThatMightTake10Minutes : Migration
{
    ...
}

10 minutes for a migration!!!  I've got one - it iterates over a cursor which
corrects bad data in a very large table.  After correcting the data, it applies 
a new
stricter constraint so that the data can't be damaged again.

Original comment by rawdatad...@gmail.com on 23 Dec 2008 at 9:56

[GoogleCodeExporter]

I am still having the issue, 90s is still not suffisant in some scenarios (some 
of them include big SQL Scripts which can take a while to complete)...

Original comment by homebo...@gmail.com on 10 Jul 2010 at 12:38

[GoogleCodeExporter]

having the same issue. Have to create an index on the large table..

Original comment by demchenk...@gmail.com on 14 Dec 2010 at 12:31

[GoogleCodeExporter]

had the same issue (creating index on a very large table), looked around the 
source and decided to do this work-around (i know its a bit ugly but it does 
what it should):

public static class MigrationsExtender
    {
        public static int ExecuteNonQuery(this ITransformationProvider itp, string sql, int timeout)
        {
            itp.Logger.Trace(sql);
            itp.Logger.ApplyingDBChange(sql);
            IDbCommand ic = itp.GetCommand();
            ic.CommandText = sql;
            ic.CommandTimeout = timeout;
            try { return ic.ExecuteNonQuery(); }
            catch (Exception up)
            {
                itp.Logger.Warn(up.Message);
                throw up;
            }

        }
    }

Original comment by abeldedo...@gmail.com on 20 May 2011 at 10:32