SQL CLR could not be executed

Question

I understand that waiting completes the rest of the executable code when they return from waiting. I am trying to get this to work in sql clr, and this is not working as the expected process and the codes under it are not executing. In the debugging method, the control simply returns after the wait string has completed.

how can I do this work, since the requirement for clr must be met without blocking the main thread, so that other work continues in db too. I really need this to work, and have been on it for 2 days.

NB This works well if the method is synchronized. And the saved proc collection is registered as unsafe in the sql server. I am using mssql 2012 and visual studio 2015.

public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static async void sp_push_stock_update(SqlString transactionPrimaryKey, SqlString transactionType) { using (SqlConnection conn = new SqlConnection("context connection=true")) { StockUpdateClient.stock_fetcher _stockFetcher = new StockUpdateClient.stock_fetcher(conn); List<String> skuList = await new System.Threading.Tasks.Task<List<string>>(()=> _stockFetcher.getItems(transactionPrimaryKey, transactionType)); //Code does not get here performLogging(skuList): } } } 

Answer 1

requirement to execute clr without blocking the main thread

Have you tried without streaming processing and experienced that you really blocked the stream? You might be making this too complicated. However, the following options are available:

• Surround a potentially long-term call with Thread.BeginThreadAffinity() and Thread.EndThreadAfinity() , as recommended in this comment .

• Do the multithreading in what constitutes the "old-fashioned" way by creating a new Thread that handles the long-term call and wait for it to complete in the loop that calls thread.sleep(x);

   Thread _RunYouLongTime = new Thread(delegate); _RunYouLongTime.Start(); while (_RunYouLongTime.IsAlive) { System.Threading.Thread.Sleep(100); } 

• If the long-term call is an HttpRequest / web service, increase the ServicePointManager.DefaultConnectionLimit property. This should probably be done no matter how, or even if you are executing threads!