Getting error in CLR execution

Question

I think this may be the same as This Issue

I have also referenced it but still not able to get the solution of my problem. This is the first time I am creating any CLR and getting problem.

I have delveloped a CLR into C# as::

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime GetTimeForCompanyID(SqlInt64 CompanyID)
    {
        string strZoneID;
        string connStr = @"data source=.;initial catalog=dbName;Integrated Security=SSPI;user id=RJ;password=RJ@123;enlist=false;";
        using (SqlConnection connection = new SqlConnection(connStr))
        {
            connection.Open();

            using (SqlCommand select = new SqlCommand(
                "select SettingValue from CompanySetting where CompanyID="+CompanyID+" and Setting='TimeZone'",
                connection))
            {
                using (SqlDataReader reader = select.ExecuteReader())
                {
                        strZoneID = reader.GetString(0);

                }
            }
        }


        TimeZoneInfo tzi = TimeZoneInfo.FindSystemTimeZoneById(strZoneID);
        DateTime result = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, tzi);
        SqlDateTime Final = result;
        return Final;
    }




}

and the creation of Assembly into SQL is as::

1) I have created the Assembly Key first into master database as::

use master
CREATE ASYMMETRIC KEY MyDllKey FROM EXECUTABLE FILE = 'C:\Temp\CueBusinessFunctions.dll'
CREATE LOGIN MyDllLogin FROM ASYMMETRIC KEY MyDllKey
GRANT EXTERNAL ACCESS ASSEMBLY TO MyDllLogin

2) Created the Assembly as::

use Learn
CREATE ASSEMBLY CueBusinessFunctions FROM 'C:\Projects\CueBusinessFunctions.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS 
GO

3) Creating CLR UDF as::

CREATE FUNCTION [dbo].[GetTimeForCompanyID](@CompanyID bigint)
RETURNS datetime
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME CueBusinessFunctions.UserDefinedFunctions.GetTimeForCompanyID;
GO

But the Problem is when executing the UDF as::

select [dbo].[GetTimeForCompanyID](1)

I am getting the Error as::

    Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetTimeForCompanyID": 
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

System.Security.HostProtectionException: 
   at UserDefinedFunctions.GetTimeForCompanyID(SqlInt64 CompanyID)
.

And what have you tried? Does it work if you avoid the external sql calls? How about without the timezone processing? Does it still fail if your method were hardcoded to return null? — sisve, Mar 21 '14 at 06:59

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

The use of the TimeZoneInfo structure in a SQLCLR function carries with it the warning that this particular data type is, in fact, marked with the MayLeakOnAbort attribute. That is why you're getting the exception you're seeing.

You may find some helpful information on this thread: How does one use TimeZoneInfo in a SQLCLR assembly in SQL Server 2012

edited May 23 '17 at 12:06

Community

1
1

answered Mar 22 '14 at 22:17

David W

10,062
34
60

score 0 · Answer 2 · edited May 23 '17 at 12:20

David is right. As soon as you create a TimeZoneInfo in the code, you'll end up with this error. I have a similar problem to solve (converting local times to UTC times). I will write a piece of C# code that will read records from the DB, do the calculations and write back the results. I would suggest this to you too. I am also worried about the performance of your bit of code. Each time you would call your function, you build a new SQL connection.
check out How does one use TimeZoneInfo in a SQLCLR assembly in SQL Server 2012

Getting error in CLR execution

2 Answers2