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