我有一个CLR触发器,它使用
代码如下:
BeginInvoke
调用存储过程。在存储过程中,我尝试调用SqlComman,但是出现以下错误:所请求的操作需要一个SQL Server执行线程。当前线程是由用户代码或其他非SQL Server引擎代码启动的。
我必须在存储过程中执行这些操作,并且必须使用BeginInvoke
调用存储过程,因为需要等待。代码如下:
[SqlProcedure()]
public static void MySendData( String crudType, String sourceTable, ... ) {
SqlCommand sqlDeleteComm;
String temp = String.Empty;
using( SqlConnection conn = new SqlConnection( "context connection=true" ) ) {
sqlDeleteComm = new SqlCommand( "DELETE FROM #TEMP_TABLE" );
sqlDeleteComm.Connection = conn;
try {
conn.Open();
sqlDeleteComm.ExecuteNonQuery();
conn.Close();
} catch( Exception ex ) {
// --- here ---
// The requested operation requires a Sql Server execution thread. The current thread was started by user code or other non-Sql Server engine code.
}
}
...
}
[Microsoft.SqlServer.Server.SqlTrigger( Name = "MyTrigger", Target = "MyTable", Event = "FOR UPDATE, INSERT, DELETE" )]
public static void MyTrigger() {
SqlTriggerContext myContext = SqlContext.TriggerContext;
MyDelagate d;
SqlCommand sqlComm;
SqlDataReader reader;
if( connection.State != ConnectionState.Open ) {
connection.Open();
}
switch( myContext.TriggerAction ) {
case TriggerAction.Update:
sqlComm = new SqlCommand( "SELECT X, Y FROM Inserted" );
sqlComm.Connection = connection;
reader = sqlComm.ExecuteReader();
try {
reader.Read();
d = new MyDelagate( MySendData );
d.BeginInvoke( "Update", "MyTable", ( Int32 )reader[ 0 ], ( Int32 )reader[ 1 ], null, null );
} catch( Exception ex ) {
} finally {
reader.Close();
}
break;
...
}
}
我应该如何在存储过程中调用SQL查询?