Specify timeout for .CrossApplySqlServerQuery and .ToSqlCommand

[mickvikt]

Is there a way to specify the SQL command execution timeout for .CrossApplySqlServerQuery and .ToSqlCommand? I'm trying to run a stored proc using .CrossApplySqlServerQuery and consume the result set that it returns and it fails me with a timeout.

As I found out by googling, default CommandTimeout for SqlConnection and OdbcConnection is 30 seconds which is not enough.

Is there a way to go around this? How do you solve this in your production environments, because I suspect not all queries/stored proc calls can be completed in under 30 seconds.

[paillave]

very soon, a new realease will be done containing improvments for Oledb and Odbc. In the meantime, can you see if you can set this timeout in the connection string? (I believe it may be possible)

[mickvikt]

Nice to hear about upcoming release.

Now regarding the timeout, unfortunately you cannot specify it in the connection string.

It is being set on the Command object.

On Tue, Jul 4, 2023, 09:44 Stéphane Royer @.***> wrote:

very soon, a new realease will be done containing improvments for Oledb and Odbc. In the meantime, can you see if you can set this timeout in the connection string? (I believe it may be possible)

— Reply to this email directly, view it on GitHub https://github.com/paillave/Etl.Net/issues/463#issuecomment-1619609311, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGGQZ2F42L46M3ERJT32QBTXOO3U7ANCNFSM6AAAAAAZ4P3BCM . You are receiving this because you authored the thread.Message ID: @.***>

[paillave]

Indeed, I mixed with the connection timeout.

[mevgdev]

Any update about this topic?

I have same problem: versión 2.0.47

// Configuración de la conexión a SQL Server
        using (var cnx = new SqlConnection("Server=xxxx;Database=yyyyy;User Id=zzzz;Password=ppppp;Connection Timeout=900;"))
        {
            cnx.Open(); 
            var executionOptions = new ExecutionOptions<Setting>
            {
                Resolver = new SimpleDependencyResolver().Register(cnx),
                TraceProcessDefinition = DefineTraceProcess
            };
            
            var res = await processRunner.ExecuteAsync(setting, executionOptions);

            Console.WriteLine(res.Failed ? "Failed" : "Succeeded");
            Console.WriteLine(_maxTagIdMovenment);
            if (res.Failed)
                Console.WriteLine($"{res.ErrorTraceEvent.NodeName}({res.ErrorTraceEvent.NodeTypeName}):{res.ErrorTraceEvent.Content.Message}");
        }

        DefineProcessSaveId(appRootPath, setting);
        Console.WriteLine("task finished");

    }

and DefineProcess:

 private static void DefineProcess(ISingleStream<Setting> contextStream)
    {
        var stream = contextStream
            .Select("build criteria", i => new { MinIdSQL = i.MinIdSQL })
                .CrossApplySqlServerQuery("Get data from data base", o => o
                .FromQuery(@"
                WAITFOR DELAY '00:00:35'
                SELECT 1, 2, 3")
                .WithMapping<DCHCuentaCascosDto>()
                                )
        .Do("Set data to mongo", i =>
        {
            _maxTagIdMovenment = i.id;
            _collection.InsertOne(i);
        });


    }

Hypothetically, it should work, but it is not considering the timeout in the connection string.