locked
Is it possible to create thread & start from CLR Stored Proc RRS feed

  • Question

  • My simple CLR Stored procedure is as below:

     [Microsoft.SqlServer.Server.SqlProcedure]
      public static int MyParallelStoredProc(string name1, string name2)
       {
                Thread t = null;
                Worker wth = null;
                int parallel = 2;
                Object[] obj = new object [parallel];
                SqlPipe p;
                p = SqlContext.Pipe;
                
                for (int i = 0; i < parallel; i++)
                {
                    if (i == 0)
                        wth = new Worker(name1);
                    else
                        wth = new Worker(name2);
                    t = new Thread(new System.Threading.ThreadStart(wth.WorkerProc));
                    t.Name = "Thread -" + i.ToString() + ":";
                    t.Start();
                    p.Send(t.Name + ":Started");
                    obj[ i] = t;
                }
                for (int i = 0; i < parallel; i++)
                {
                    t = (System.Threading.Thread)obj[ i];
                    t.Join();
                    p.Send(t.Name + ":Finished");
                }
                return 0;
            }

    The worker class implementing Thread Proc:

    public class Worker
        {
            private string Name;

            public Worker(string name)
            {
                SqlPipe p;
                p = SqlContext.Pipe;
                Name = name;
                p.Send("In Constructor:" + Name);
            }

            public void WorkerProc()
            {
                SqlPipe p;
                p = SqlContext.Pipe;
                for (int i = 0; i < 10; i++)
                    p.Send(i.ToString()+":"+Name);
            }
        }

    The assembly is registered with  UNSAFE permission set.

    CREATE ASSEMBLY
    ThreadTest
    FROM
    'C:\\ThreadTest\bin\Debug\ThreadTest.dll'
    WITH
    permission_set = unsafe;
    GO

    CREATE PROC ParallelStoredProc
    @Name1 NVARCHAR(1024),
    @Name2 NVARCHAR(1024)
    AS
    EXTERNAL NAME ThreadTest.[MyTest.ThreadTest].MyParallelStoredProc

    When I invoke the the stored procedure from T-SQL script as below,

    EXEC ParallelStoredProc @Name1, @Name2

    the thread class constructor gets called; but the 'WorkerProc' does not execute ?

     

    Whether an UNSAFE assembly is allowed to spawn threads

    inside SQL Server ?  

    Wednesday, April 5, 2006 1:02 PM

Answers

  • Your code works correctly to start and run threads under unsafe.  The reason you think it doesn't work is because the SqlContext connection is not available on new threads, so you can't use it to Pipe.Send information back.

    If you try/catch for exceptions in your WorkerProc, you should see an error like the following:

    "The requested operation requires a Sql Server execution thread.  The current thread was started by user code or other non-Sql Server engine code."

    Steven

    Wednesday, April 5, 2006 6:04 PM

All replies

  • Your code works correctly to start and run threads under unsafe.  The reason you think it doesn't work is because the SqlContext connection is not available on new threads, so you can't use it to Pipe.Send information back.

    If you try/catch for exceptions in your WorkerProc, you should see an error like the following:

    "The requested operation requires a Sql Server execution thread.  The current thread was started by user code or other non-Sql Server engine code."

    Steven

    Wednesday, April 5, 2006 6:04 PM
  •  

    Thanks steve. Your input was very useful.

    If I use SqlConnection in WorkerProc, the thread gets aborted

    and goes into "Stopped" state.

    It means the main CLR Stored proc can only execute the T-SQL commands ?

    WorkerProc's are restricted to computations.

    Thursday, April 6, 2006 1:26 PM
  • Hi Steven,

     

    I am getting this error "The requested operation requires a Sql Server execution thread.  The current thread was started by user code or other non-Sql Server engine code." when i tried to create & open a connection inside thread pool. If i do the same out side pool then i dont see any issues.

     

    Can you please suggest me on the same


    Thanks

    Adi

    Saturday, March 15, 2008 11:54 AM
  • Hi!

     

    There is no way you can use context connection from non-main thread.

     

    However, you can create a "usual" (== network, loopback) connection to your server from a non-main thread.

     

    Thank you!

     

    Wednesday, March 26, 2008 12:40 AM
  • Vadim,

     

    Could you elaborate a little on your comment? 

     

    If I have a CLR stored proc and I need to create a couple threads to do some work and those threads need to call some more stored procedures from the database, can I do that and if so how? 

     

    Also the stored procedures that need to be called from the worker threads are in schemas that only certain network user accounts have procedures to so I need to call the procedures from the worker threads as the user that called the main CLR stored proc?

     

    Thanks

    Erik

     

    Friday, September 19, 2008 6:21 AM
  • Erik,

     

    The only way that this would work is for your users to have to have Windows Authenticated Accounts, or for you to grant the SQL Service Account access to the procedures.  If you use the first option I mentioned, then you have to create an WindowsIdentity object on the main thread following this Example:

     

    Using CLR Impersonation to Access Resources Outside of SQL Server

     

    Then you are going to need to pass that into the new thread and then inside the thread make the WindowsImpersonationContext identity impersonation, create the connection, make the stored procedure call, close the connection, and revert the token.

     

    Friday, September 26, 2008 12:40 AM
  • hi ,.. do you solve (Is it possible to create thread &amp; start from CLR Stored Proc) ISSUE?
    Thursday, January 29, 2009 9:23 PM