Connection timeout in SMO

Question

I'm inserting a high volume of rows into a database and trying to establish a primary key on it. If I create the table and establish a key right away, inserting data takes 10x as long even with SQLBulkCopy command. So this is not a viable option. What I'm trying to do now is insert the data, and after it is all inserted, create the primary key using SMO. The issue is that I keep getting timeout exceptions on the alter() command even with the timeout set to 0 in the connection string. Any ideas on how to get around this?

connectionString.ConnectTimeout = 0;

ServerConnection scon = null;
using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
{
    conn.Open();
    try
    {
        scon = new ServerConnection(conn);
        Console.WriteLine("Server Connection Timeout: " + scon.ConnectTimeout);
        Server serv = new Server(scon);
        Database db = serv.Databases[connectionString.InitialCatalog];
        Table table = db.Tables[tableName];
        Index i = new Index(table, "pk_" + table.Name);
        i.IndexKeyType = IndexKeyType.DriPrimaryKey;
        foreach (String s in PrimaryKey)
        {
            i.IndexedColumns.Add(new IndexedColumn(i, s.Trim()));
        }
        table.Indexes.Add(i);
        table.Alter();
        scon.Disconnect();
    }
    finally
    {
        conn.Close();
    }
}

@MichaelPerrenoud For clean and consistent code I would like to. Are you thinking to just create a SQL script and run it? — dko, Feb 21 '13 at 15:39
You dont need conn.close(), you've encapsulated the connection in a Using Statement, the connection will automatically be closed. — Derek, Feb 21 '13 at 15:42
@Derek I'm aware of this, right now I'm just trying to get the timeout issue fixed at the table.Alter(); — dko, Feb 21 '13 at 15:48
scon = new ServerConnection(conn); scon.ConnectionContext.StatementTimeout = 0;Read this, it may help :- http://stackoverflow.com/questions/10511984/how-to-set-commandtimeout — Derek, Feb 21 '13 at 15:59

score 5 · Accepted Answer · edited May 23 '17 at 11:44

5

Apparently ServerConnection also has a statement timeout. SMO is full of these hidden timeouts. Including SQLBulkCopy. However, thanks to @Derek for pointing this out. The answer is you have to set the StatementTimeout = 0. I am testing this now but it appears to be the answer.

How to set CommandTimeout

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnection.statementtimeout.aspx

edited May 23 '17 at 11:44

Community

1
1

answered Feb 21 '13 at 16:48

dko

698
1
6
18

Unfortunately after 2.5 hours I get a different exception. Still something do do with a timeout. However, after over 2 hours of trying to get a Primary Key applied, I think it is justified. – dko Feb 22 '13 at 16:42

Connection timeout in SMO

1 Answers1