Question
I am using Sql​Bulk​Copy.Write​To​Server (C#) to copy billions of rows, and which takes hours to complete. Sometimes I need to stop the operation for some urgent tasks, but I don't know how to do so. Can I do something on the arriving of the notification event (with Sql​Bulk​Copy.NotifyAfter and Sql​Bulk​Copy.SqlRowsCopied)?
Accepted Answer
The MSDN documentation says:
If the user wants to cancel the operation from the event, the Abort property of the SqlRowsCopiedEventArgs can be used.
So the following code should do the trick:
SqlBulkCopy cp = new SqlBulkCopy("your connection string");
cp.NotifyAfter = 1000; // use here whatever number floats your boat
cp.SqlRowsCopied += (sender, eventArgs) => eventArgs.Abort = true; // add some more conditions here
Popular Answer
Can I do something on the arriving of the notification event
As a SQL administrator you can always find the session running the bulk load and KILL it, like this:
declare @spid int = (
select session_id
from sys.dm_exec_requests
where command = 'BULK INSERT')
if @spid is not null
begin
declare @sql varchar(200) = concat('kill ',@spid)
exec ( @sql )
end
If you want to do this in your code, use the Async version of WriteToServer which accepts a Cancelation Token. eg
static void Main(string[] args)
{
using (var src = new SqlConnection("server=.;database=tempdb;integrated security=true"))
using (var dest = new SqlConnection("server=.;database=tempdb;integrated security=true"))
{
src.Open();
dest.Open();
var cmd = new SqlCommand("create table #t(id int)", dest);
cmd.ExecuteNonQuery();
bool cancel = false;
var cancelationTokenSource = new CancellationTokenSource();
var srcCmd = new SqlCommand("select row_number() over (order by (select null)) id from sys.objects o, sys.columns c, sys.columns c2", src);
using (var rdr = srcCmd.ExecuteReader())
{
var bc = new SqlBulkCopy(dest);
bc.NotifyAfter = 10000;
bc.SqlRowsCopied += (s, a) =>
{
Console.WriteLine($"{a.RowsCopied} rows copied");
if (cancel)
{
dest.Close();
}
};
bc.DestinationTableName = "#t";
bc.ColumnMappings.Add(new SqlBulkCopyColumnMapping("id", "id"));
var task = bc.WriteToServerAsync(rdr, cancelationTokenSource.Token);
Console.WriteLine("Hit any key to cancel the bulk load");
while (!task.Wait(1000))
{
if (Console.KeyAvailable)
{
cancelationTokenSource.Cancel();
try
{
task.Wait();
}
catch (AggregateException ex)
{
Console.WriteLine(ex.InnerException.Message);
Console.WriteLine("WriteToServer Canceled");
break;
}
}
}
Console.WriteLine("Hit any key to exit");
return;
}
}
}