using (SqlConnection remoteConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ArchiveDb"].ConnectionString)) { using (SqlCommand remoteCmd = new SqlCommand("Get_RecordsToArchive", remoteConnection)) { remoteCmd.CommandType = CommandType.StoredProcedure; remoteCmd.Parameters.AddWithValue("@ClientId", criteria.ClientId); remoteCmd.Parameters.AddWithValue("@MatchSetId", criteria.MatchSetId); remoteCmd.Parameters.AddWithValue("@AgedDate", criteria.AgedDate); remoteCmd.Parameters.AddWithValue("@BatchTableName", criteria.SourceTableName); remoteCmd.Parameters.AddWithValue("@DisplayCol", criteria.DisplayCol); try { remoteConnection.Open(); SqlDataReader remoteReader = remoteCmd.ExecuteReader(CommandBehavior.CloseConnection); using (SqlConnection localConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ArchiveDb"].ConnectionString)) { try { localConnection.Open(); SqlBulkCopy localBulkCopy = new SqlBulkCopy(localConnection); localBulkCopy.DestinationTableName = criteria.TargetTableName; localBulkCopy.BatchSize = criteria.BatchSize; localBulkCopy.NotifyAfter = criteria.NotifyAfter; localBulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(ArchiveBatchTable_SqlRowsCopied); localBulkCopy.WriteToServer(remoteReader); } finally { localConnection.Close(); } } } finally { remoteConnection.Close(); } } }