Is it possible to cancel a Sql​Bulk​Copy.Write​To​Server (c#)?

c# sqlbulkcopy sql-server

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;
            }




        }
    }


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why