Multiple Insert transaction connection timeout - ADO.NET - SQL Server

Question

I have a transaction with multiple inserts. All of the inserts work fine, except one. I verified the parameters, the spelling, all of it, and it seems I'm not figuring it out. It gives me the error:

Timeout expired. The timeout period elapsed prior to completion of the operation 
or the server is not responding.

My transaction looks like this:

SqlConnection db = new SqlConnection(connString);
DataSet dataSet = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
using (db)
{
    db.Open();
    SqlTransaction trans = db.BeginTransaction();
    try
    {
        //insert into COMMSignalDefinition !!Problem HERE
        da.InsertCommand = 
            new SqlCommand("INSERT INTO COMMSignalDefinition(Name) " 
                           + "VALUES (@name)", db, trans);

        da.InsertCommand.Parameters.Add("@name", SqlDbType.NVarChar);
        foreach (DataRow row in ds.Tables["COMMTerminalSignal"].Select())
        {
            da.InsertCommand.Parameters[0].Value = row.ItemArray[1];
            da.InsertCommand.ExecuteNonQuery();
        }

        // insert into COMMSignalExceptionDefinition -- names
        da.InsertCommand = 
            new SqlCommand("INSERT INTO COMMSignalExceptionDefinition(Name) " 
                           + "VALUES (@name)", db, trans);
        da.InsertCommand.Parameters.Add("@name", SqlDbType.NVarChar);
        foreach (DataRow row in ds.Tables["COMMSignalExceptionDef"].Select())
        {
            da.InsertCommand.Parameters[0].Value = row.ItemArray[1];
            da.InsertCommand.ExecuteNonQuery();
        }

        trans.Commit();
        MessageBox.Show("You have successfully imported your Settings. " 
                        + "You can now exit the program.", 
                         "Success",
                         MessageBoxButtons.OK, 
                         MessageBoxIcon.Information);
    }
    catch (Exception e)
    {
        trans.Rollback();
        MessageBox.Show(e.Message,
                        "Error", 
                        MessageBoxButtons.OK, 
                        MessageBoxIcon.Error);
    }
}

I have more inserts which work fine (I deleted the rest of them) and I moved the one with problems at the beginning. My question is what could I possibly do wrong? I even verified if the "problematic" query is sent to the server, with SQL Server Profiler, and it does! And if I execute it in SQL Server Management studio, it works too.

Connection Timeout is set to 30

Can You please give me some leads ? SQL Server version is 2005 ! Thank You!

What's the number of records in the tables "COMMTerminalSignal" and "COMMSignalExceptionDef"? How long does it take to run this in SQL Server Management Studio? — Jacco
– Jacco, Commented Jan 18, 2013 at 9:42
COMMTerminalSignal has 2 records, COMMSignalExceptionDef (the insert works on this one) has 1 record — darkdante
– darkdante, Commented Jan 18, 2013 at 9:47
@Jacco yes they are appearing, since the other inserts work fine (and the rows are added in database) when i remove the one with problems ... — darkdante
– darkdante, Commented Jan 18, 2013 at 10:12

General Grievance · Accepted Answer · 2022-05-04 17:39:20Z

3

After hours of digging, it came out I did some tests in Management Studio, where I tested some transactions without committing them at all. So it was waiting for a commit, and I kept making, or trying to make inserts ... !

edited May 4, 2022 at 17:39

General Grievance

5,12039 gold badges39 silver badges60 bronze badges

answered Jan 18, 2013 at 12:34

darkdante

7171 gold badge18 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jacco Over a year ago

I would just rewrite your own answer, so it has less smileys and mark it as "The answer". I guess we've all been there more than once!

Jacco · Accepted Answer · 2013-01-18 09:48:35Z

1

If this is a pretty long running operation, you can try to change the Command Timeout.

answered Jan 18, 2013 at 9:48

Jacco

3,2721 gold badge22 silver badges30 bronze badges

Comments

Alex · Accepted Answer · 2013-01-18 10:17:26Z

1

Check your connection string. Maybe you have some mistake in your connString var
Check your SqlDataAdapter command timeouts. They may be the cause of the exception (see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx) Usually such exception is the result of either long running tasks or uncommitted transactions.

edited Jan 18, 2013 at 10:17

answered Jan 18, 2013 at 9:52

Alex

8,9473 gold badges44 silver badges59 bronze badges

Comments

Tim Schmelter · Accepted Answer · 2013-01-18 09:36:05Z

0

Connection Timeout is set to 1

SqlConnection.ConnectionTimeout Property

The time (in seconds) to wait for a connection to open. The default value is 15 seconds.

So you have set the timout to 1 second. Does that answer your question?

Remarks: You can set the amount of time a connection waits to time out by using the ConnectTimeout or Connection Timeout keywords in the connection string. A value of 0 indicates no limit, and should be avoided in a ConnectionString because an attempt to connect waits indefinitely.

answered Jan 18, 2013 at 9:36

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

1 Comment

darkdante Over a year ago

Thank You for the answer, @Tim Schmelter, but I have tried with the default Conn Time, even with 0, with the same results :(. The problem is that when I debug the code, it freezes at that line untill the exception is caught ...

Collectives™ on Stack Overflow

Multiple Insert transaction connection timeout - ADO.NET - SQL Server

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related