problem withAsync SqlComman

Question

I have problem with Timeout, when I run a command through app, a timeout exception is thrown, but when I run it directly in sql there is no timeout exception!

my SP take about 11 min when I run it directly. for solving this issue, I found below code here, but It doesn't work properly! Immediately after beginExecute, IAsyncResult.iscomplete become true !!!!

where is the problem ?

IAsyncResult result = command.BeginExecuteNonQuery();

    int count = 0;
    while (!result.IsCompleted)
    {
        Console.WriteLine("Waiting ({0})", count++);
        System.Threading.Thread.Sleep(1000);
    }
    Console.WriteLine("Command complete. Affected {0} rows.",
    command.EndExecuteNonQuery(result));

regards

Oded · Answer 1 · 2010-04-04T08:09:41.933

0

A connection string will default to a 15 second timeout. See on MSDN.

You can change the timeout on the connection string to last longer (connection timeout=600, for a 10 minute timeout).

See this site for more about connection strings.

Having said that, you should look at optimizing your database and/or stored procedure. 11 minutes for a stored procedure is very very long. Do you have the correct indexes on your tables? Is you stored procedure written in the most optimal way?

Update:

Have you made sure you are using the correct command and that the results are correct? IsComplete being true almost immediately suggests that the command has indeed finished.

edited Apr 04 '10 at 08:09

answered Apr 04 '10 at 07:52

Oded

489,969
99
883
1,009

Thanks for answering, I increased timeout in connection string but it did not work. in this sp I have to work on 3 table hierarchically, I have 3 cursor and used them hierarchically for this 3 table! – Alibm Apr 04 '10 at 07:58
I have to read all of these record and insert them for newyaer so I need new IDs for child tables – Alibm Apr 04 '10 at 08:18

score 0 · Accepted Answer · answered Apr 04 '10 at 08:04

0

Increase the command timeout instead (SqlCommand.CommandTimeout) which by default is 30 seconds.

answered Apr 04 '10 at 08:04

Mehmet Aras

5,284
1
25
32

I did not know that there is a another timeout in Command! Thanks, My main problem is solved! But what happen to IAsync ? – Alibm Apr 04 '10 at 08:19
Are you getting any results? What are you specifying as the command text? – Mehmet Aras Apr 04 '10 at 08:48

problem withAsync SqlComman

2 Answers2