|
At some point you might want to Bulk Copy data from one database to another, either for archiving, or for reporting. Whatever your reason, if you choose to implement this with CLR, there is an important gotcha that you need to be aware of. You can not use SqlBulkCopy with the context connection.
Recently on a MSDN Forums someone asked a question regarding a problem they encountered using SqlBulkCopy in SQL CLR. Since I had never used this in CLR, I went about creating a CLR Procedure that would take all the data in a column of one table and use the SqlBulkCopy class to bulk insert this data into a table in another database. I learned an important lesson in building this Procedure; SqlBulkCopy does not work on a connection using "context connection=true". If you attempt a SqlBulkCopy, you will get the following error:
Msg 6522, Level 16, State 1, Procedure usp_MoveBulkDecimals, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "usp_MoveBulkDecimals":
System.InvalidOperationException: The requested operation is not available on the context connection.
System.InvalidOperationException:
at System.Data.SqlClient.SqlBulkCopy.CreateOrValidateConnection(String method)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
at StoredProcedures.usp_MoveBulkDecimals()
I just accepted this at first, and went about creating a secondary impersonated connection into the local sql server. However, once I had the process working as expected, I decided to go about researching this some more. I found the following article on MSDN BOL:
Restrictions on Regular and Context Connections
This article is extremely important to anyone building database objects in SQL CLR.
|