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.

1 comment:

Anonymous said...

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

Followers

About Me

My photo
Email me: blog@postjobfree.com