Random Thunks: Bulk Copy within a Sql Server CLR Stored Procedure

Whilst it’s not immediately obvious, one can perform a Bulk Copy operation as part of a Sql Server CLR Stored Procedure. There are however some gotcha’s.

First off is that you need to stay away from Context Connections for the actual operation. Whilst you can technically use a Context Connection for the first part, I’d personally not recommend you do for reasons that will become apparent shortly.

The code below is taken from one of my procedures using a homebrew class that provides me with information regarding a table – including the various elements of a fully qualified 4 part name (Server.Database.Schema.Table).

The first part of the code handles the bulk import:

  using (SqlConnection sourceCx = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI;", sourceTable.ServerName, sourceTable.DatabaseName)))
            {
                sourceCx.Open();
 
                using (SqlCommand command = new SqlCommand(string.Format("Select * From {0};", sourceTable.FqTableName2Part), sourceCx))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(string.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI;", destinationTable.ServerName, destinationTable.DatabaseName)))
                        {
                            bulkCopy.DestinationTableName = destinationTable.FqTableName2Part;
                            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                            bulkCopy.DestinationTableName = destination;
                            bulkCopy.BatchSize = 10000;
                            bulkCopy.NotifyAfter = bulkCopy.BatchSize;
                            bulkCopy.WriteToServer(reader);
                            bulkCopy.Close();
                        }
                    }
                }
            }
 

As you can see I’m using two SSPI connections in place of the Context based Connection that’s normally provided. I decided to use SSPI to avoid needing to store user names and passwords. Your mileage and methodology may vary.

The next section is the reason why I advise not using a Context Connection for the DataReader:

 static void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
     {
         string call = string.Format("RaisError('{0} rows copied', 0, 0) With NoWait", e.RowsCopied);
         SqlContext.Pipe.ExecuteAndSend(new SqlCommand(call));  
     }
 

In order for this event to work correctly the Context Connection must be free – which will not be the case if you use it for the DataReader. Running the RaisError via ExecuteAndSend ensures the results are not gobbled up by the any special pipe that SqlCommand.ExecuteNoQuery creates (see Vadim Tryshev’s excellent post on RAISERROR in CLR Routines). Finally I use a 0 for both the State and Severity to avoid any subsequent messages that RaisError would otherwise produce.

Sql Server 2008, SP1