Connection timeout in SMO

Question

I insert a large amount of rows into the database and try to set the primary key on it. If I create a table and immediately create a key, inserting the data will take 10 times, even with the SQLBulkCopy command. So this is not a viable option. What I'm trying to do now is insert data, and after everything is inserted, create a primary key using SMO. The problem is that I keep timeout exceptions in the alter () command, even when the timeout is 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(); } } 

Answer 1

Obviously, ServerConnection also has an instruction timeout. SMO is full of these hidden timeouts. Includes SQLBulkCopy. However, thanks @Derek for this. The answer is that you should set StatementTimeout = 0. I am testing this now, but it seems to be the answer.

How to install CommandTimeout

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