Thursday, January 10, 2008

SqlBulkCopy in CLR SQL Stored Procedure

When I was trying to run SqlBulkCopy in CLR (C#) SQL Stored Procedure, I've got at exception:
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)


It seems that it's impossible to run SqlBulkCopy within "context connection" ...

Here's the only tip I was able to find:

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-clr/9/SqlBulkInsert

> Does SqlBulkInsert not run in CLR? Why does not?

Nope. It (BulkInsert) uses a different provider library if I'm understanding
things correctly.

Not much ... I guess CLR Stored Procedures work fast enough already...


Batch Update doesn't work in CLR Stored Procedures [on context connection] either.


When I tried to execute this code:
SqlDataAdapter da = new SqlDataAdapter();
cmd.UpdatedRowSource = UpdateRowSource.None;
da.UpdateCommand = cmd;
da.UpdateBatchSize = 1000;
da.Update(table);


I got this exception:
"Batching updates is not supported on the context connection."
Note the grammar: "... updates IS not supported ..."

It seems that nobody really cares about batch functionality in CLR SP.


What do you think? Please, let me know.

Followers

About Me

My photo
Email me: blog@postjobfree.com