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.
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:

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

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.

1 comment:

Anonymous said...

got the same problem. please let us know if you learned something new about it


About Me

My photo
Email me: