SQL CLR awaitable not getting executed

Question

It is my understanding that the awaitable completes the remaining part of the executing code when they return back from the wait. I am trying to get this to work in an sql clr, and this is not working as both awaited process and codes beneath it are not getting executed. In debug method, the control just returns after executing the await line.

how can i make this work, as the requirement is for clr to be executed without blocking the main thread so that other work can continue well in the db. I really need this to work, and have been on it for 2 days now.

NB This works well if method is sync. And the stored proc assemble is registered as unsafe in 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):


        }

    }
}

You should never use `async void` unless you are writing a event handler and `async Task` [is not supported by sqlclr](https://social.msdn.microsoft.com/Forums/en-US/2f43b23a-ebf7-4f48-9331-654587730d1b/async-and-sqlclr?forum=async) — Scott Chamberlain, Sep 27 '16 at 23:59
@ScottChamberlain the method is void by default and can be seen when a new sql c# clr is added to the project. i only decorated it with async because this is a requirement to use await within the method body, are you saying i could change the method return type? what other options do i have for threading aside async task? — Edwin O., Sep 28 '16 at 03:05
For a `SqlProcedure`, th only two valid return types should be: `void` and `SqlInt32`. The `SqlInt32` would be the return value (i.e. `RETURN 5;`) that can be captured using: `DECLARE @ReturnVal INT; EXEC @ReturnVal = dbo.SomeProc;`. — Solomon Rutzky, Sep 28 '16 at 06:04
Besides using `async-await` inside SQL CLR , you are using the `Task` constructor and never starting the task. I suspect that, as soon as you start it, you'll get a runtime error. — Paulo Morgado, Sep 28 '16 at 06:32

score 2 · Answer 1 · edited May 23 '17 at 12:01

the requirement is for clr to be executed without blocking the main thread

Have you tried without the threading and experienced that it did indeed block the thread? It is possible that you are over-complicating this. Still, some options available are:

Surround the potentially long-running call with Thread.BeginThreadAffinity() and Thread.EndThreadAfinity() as recommended in this comment.
Do multi-threading in what amounts to the "old-fashioned" way by creating a new Thread that handles the long-running call, and wait for it to complete in a loop that calls thread.sleep(x);:
```
Thread _RunYouLongTime = new Thread(delegate);
_RunYouLongTime.Start();
while (_RunYouLongTime.IsAlive)
{
  System.Threading.Thread.Sleep(100);
}
```
If the long-running call is a HttpRequest / Web Service, increase the ServicePointManager.DefaultConnectionLimit Property. This should probably be done regardless of how, or even if, you do threading!

@Edwin Please see my update. I added example code in case it wasn't clear what I meant by "the old fashioned way" ;-) — Solomon Rutzky, Sep 28 '16 at 15:43

SQL CLR awaitable not getting executed

1 Answers1

Linked