SqlClient.SqlException: The wait operation timed out

Question

I'm currently trying to create a large amount of test data with numerous insert statements using code similar to below...

using (var connection = new SqlConnection(_connectionString))
{
    using (var command = new SqlCommand(query.ToString(), connection))
    {
        try
        {
            connection.Open();

            command.ExecuteNonQuery();

            return true;
        }
        catch (Exception e)
        {
            ......
        }
    }
}

My problem is that I keep getting an error

The wait operation timed out

and yet when I run the SQL statement that failed from within SQL Server Management Studio, it executes in less than 100ms. The error always seems to occur whilst inserting into the largest table which currently has 47,738,476 rows and is 1,970,696Kb in size.

I'm using:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Any, help would be most appreciated.

For bulk inserts it's generally a better idea to use [SqlBulkCopy](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx) vs lots of individual inserts. — James, Aug 02 '14 at 11:51
Try setting `command.CommandTimeout = 0` to see if it executes at all if given a longer timeout — Yuval Itzchakov, Aug 02 '14 at 11:56
What is the "Remote query timeout" setting of your DB engine? — COLINHY, Aug 02 '14 at 11:59
I have tried command.CommandTimeout = 60, I'll try setting it to command.CommandTimeout = 0. The — user3261445, Aug 02 '14 at 12:40
The 'Remote query timeout' is set to the default 600 seconds. — user3261445, Aug 02 '14 at 12:40
Open SQL profiler from tools menu of SQL server management studio and have a look at the process. — Vishal, Aug 02 '14 at 15:00

score 0 · Answer 1 · answered Aug 02 '14 at 12:25

0

Disclaimer: It may not be an answer but it solves the problem :)

Use Redgate Sql Data Generator

http://www.red-gate.com/products/sql-development/sql-data-generator/

It is not free but it is fully functional trial for some days and it will do the work for you what i want to achieve.

It had lot of option and generate real looking data.

answered Aug 02 '14 at 12:25

Moons

3,833
4
49
82

Thank you for the answer, however, I need to get to the bottom of the actual problem as it could cause an issue when not running test data. – user3261445 Aug 02 '14 at 12:28

SqlClient.SqlException: The wait operation timed out

1 Answers1

Linked