SQL CLR集成的多线程处理

[iaxk]

正在利用SQL CLR集成用C#写一个SQL Server的存贮过程. 在程序中,我需要使用多线程, 但只要我在程序中调用了Thread.Start(), 在执行这个存贮过程时, SQL Server就会报如下的错误:
A .NET Framework error occurred during execution of user-defined routine or aggregate "CalcReport":
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: Synchronization, ExternalThreading

System.Security.HostProtectionException:
............

另外,我这个Dll装载到SQL Server中用的安全性是SAFE

看了半天的MSDN, 网上也搜了很久, 都没有搜到解决办法. 望高手指教, 在此多谢!

[iaxk]

自已顶

[soaringbird]

有什么必要在存储过程里用线程呢？

[qldsrx]

也许SQL CLR不支持线程控制，如果是那样的话，你就不要使用多线程了，因为SQL本身是对一个连接一个线程的，为的是能够处理事务。

[wuyq11]

System.Security.HostProtectionException：试图执行的是由CLR主机禁止运行。
单独执行
参考

[Dobzhansky]

CLR Host 微软给了 3 个:

1. 默认 host,
2. IE Host
3. Sql Server Host,

sqlserver 那个用的 纤程(fiber), 可能内部不允许启动线程.

[Dobzhansky]

Microsoft.Press.Customizing.the.Microsoft.dot.NET.Framework.Common.Language.Runtime.Feb.2005.eBook-LiB.chm

第十四章:

[Quote=Summary]
...
To integrate the CLR with SQL Server 2005 so that scalability isn't hindered, all schedulingrelated activities must be managed directly by SQL Server. The CLR hosting API provides an abstraction called the task manager that hosts can use to supply the CLR with the basic primitives it needs to create and manage how tasks behave in the process. By using the primitives supplied by the host instead of going directly to the operating system, the CLR makes the host (SQL Server 2005, in this case) aware of all threading-related activities that occur in the process such that the execution of managed tasks can be integrated with the host's custom scheduler.
...
[/Quote]

[a296694166]

jf

[悔说话的哑巴]

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public partial class StoredProcedures
{
　[Microsoft.SqlServer.Server.SqlProcedure]

　public static void GetAuthors()
　{
　　SqlPipe sp = SqlContext.Pipe;
　　using (SqlConnection conn = new SqlConnection("context connection=true"))
　　{
　　　conn.Open();
　　　SqlCommand cmd = new SqlCommand();
　　　cmd.CommandType = CommandType.Text;
　　　cmd.Connection = conn;
　　　cmd.CommandText = "Select DatePart(second, GetDate()) " + " As timestamp,* from authors";
　　　SqlDataReader rdr = cmd.ExecuteReader();
　　　sp.Send(rdr);
　　}
　}

　[SqlProcedure]

　public static void GetTitlesByAuthor(string authorID)
　{
　　string sql = "select T.title, T.price, T.type, " + "T.pubdate from authors A" +
　　　　　" inner join titleauthor TA on A.au_id = TA.au_id " +
　　　　　" inner join titles T on TA.title_id = T.title_id " +
　　　　　" where A.au_id = ’" + @authorID + "’";
　　using (SqlConnection conn = new SqlConnection("context connection=true"))
　　{
　　　conn.Open();
　　　SqlPipe sp = SqlContext.Pipe;
　　　SqlCommand cmd = new SqlCommand();
　　　cmd.CommandType = CommandType.Text;
　　　cmd.Connection = conn;
　　　cmd.CommandText = sql;
　　　SqlParameter paramauthorID = new SqlParameter("@authorID", SqlDbType.VarChar, 11);
　　　paramauthorID.Direction = ParameterDirection.Input;
　　　paramauthorID.Value = authorID;
　　　cmd.Parameters.Add(paramauthorID);
　　　SqlDataReader rdr = cmd.ExecuteReader();
　　　sp.Send(rdr);
　　}
　}
}