I am using SQL server 2005 - 9.00.1399.06 (Intel X86)
I have written one generic CLR trigger for Audit. I have deployed the assembly through VS2008 environment. Then I created the trigger on 2 tables (Table1, Table2). I wrote 2 update statements for these two tables as follows -
It works fine. Then I tried the following -
It also works fine. But when I reverse the order of update statements as follows -
Then I am getting the error -
(1 row(s) affected)
Msg 6522, Level 16, State 1, Procedure Trigger1, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'Trigger1':
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
at System.Reflection.MethodBase.PerformSecurityCheck(Object obj, RuntimeMethodHandle method, IntPtr parent, UInt32 invocationFlags)
at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
at System.Diagnostics.TraceUtils.GetRuntimeObject(String className, Type baseType, String initializeData)
at System.Diagnostics.TraceInternal.WriteLine(String message)
at System.Diagnostics.Debug.Print(String message)
The statement has been terminated.
- Cambiado Aaron Alton martes, 30 de diciembre de 2008 6:29 CLR Question (Spostato da Transact-SQL a .NET Framework inside SQL Server)
Todas las respuestas
You are using an object (DataTable) that is inherently synchronized. There are resources configured at the CLR runtime level that are explicitly protected for a reason. MS obviously doesn't want you touching the threads of the CLR inside the DB (the host), so they explicitly protected it. Use a type that doesn't rely on thread safety/synchronization.
- Propuesto como respuesta ajweav jueves, 18 de febrero de 2010 0:27
When in a CLR, you cannot call WriteLine or Print functions. If you look in your stack, the second-highest level shows that the exception is being called from within Debug.Print(). If you look in your code:
Catch ex As Exception Debug.Print(ex.Message) End Try End Sub
Your catch statement contains this very function call. Remove this, and it should solve your problem.
And as a quick comment, I have used DataTable objects in CLRs before without incident. I doubt that they are the issue.
- Propuesto como respuesta richmintz viernes, 27 de abril de 2012 21:10