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();
}
}
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.