I have created SQL CLR Function for Timezone conversion and successfully deployed. here is my code,
public partial class UserDefinedFunctions
public static SqlDateTime TimezoneConversion(SqlDateTime _date, SqlString _currentTZ, SqlString _convertingTZ)
SqlDateTime _convertedDate = new SqlDateTime();
TimeZoneInfo _currentZone = TimeZoneInfo.FindSystemTimeZoneById(_currentTZ.Value);
TimeZoneInfo _convertingZone = TimeZoneInfo.FindSystemTimeZoneById(_convertingTZ.Value);
_convertedDate = TimeZoneInfo.ConvertTime(_date.Value, _currentZone, _convertingZone);
the assembly is Dot.net 3.5 Framework and Database is SQL Server 2008, i deployed with SAFE Permission Level. When I tried to do with External or Unsafe Mode, my visual studio 2010 shows the deployment is failed, i couldn't know much about it, because it doesn't any other message, just shows deployment failed.
I tried to execute the function, but it throws an error as below,
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "TimezoneConversion":
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: MayLeakOnAbort
at UserDefinedFunctions.TimezoneConversion(SqlDateTime _date, SqlString _currentTZ, SqlString _convertingTZ)
please some one help me to resolve the issue.
Thanks in advance.
Sr. Software Engineer,
Softura Private Limited, Chennai.
India - 600014.