Getting error when using UDF with SQL Query

Question

I have made a CLR by using SQL Project Templates into Visual studio using language C# as::

  [Microsoft.SqlServer.Server.SqlFunction()]
    public static SqlDateTime ScalarUDF(SqlInt64 CompanyID)
    {
        SqlInt64 temp = CompanyID;
        string zoneId = "Singapore Standard Time";
        TimeZoneInfo tzi = TimeZoneInfo.FindSystemTimeZoneById(zoneId);
        DateTime result = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, tzi);

        return new SqlDateTime(result);
    }

And also created the UDF into SQL using query::

-- Install Assembly
CREATE ASSEMBLY UDF_Trial FROM 'C:\Users\Rahul\Documents\visual studio 2013\Projects\UDF_Trial\UDF_Trial\bin\Debug\UDF_Trial.dll'
GO
-- Create ScalarUDF
CREATE FUNCTION [dbo].[ScalarUDF](@CompanyID bigint)
RETURNS datetime
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME UDF_Trial.UserDefinedFunctions.ScalarUDF;
GO

But while Executing the UDF I am gettin some Error:

I am executing UDF as,

select [dbo].[ScalarUDF](15)

But Getting the Exception as::

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

I have also made the Permission to UNSAFE at the time of creating Assembly as::

CREATE ASSEMBLY UDF_Trial FROM 'C:\Users\Rahul\Desktop\pro_temp\UDF_Trial.dll' WITH PERMISSION_SET = UNSAFE; GO

but in this case I am getting Error as::

CREATE ASSEMBLY for assembly 'UDF_Trial' failed because assembly 'UDF_Trial' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

@MoslemBenDhaou Give the Proper answer instead of looking into another's answer. — Rahul
– Rahul, Commented Mar 20, 2014 at 9:40
Read the StackOverflow guidelines before asking questions. stackoverflow.com/help/duplicates — Moslem Ben Dhaou
– Moslem Ben Dhaou, Commented Mar 20, 2014 at 9:41

Rahul · Accepted Answer · 2014-03-20 11:44:00Z

3

I have set the Trustworthy of the Database to ON as::

ALTER DATABASE Learn SET TRUSTWORTHY ON

and have created Assembly as ::

CREATE ASSEMBLY UDF_Blog FROM 'C:\Users\Rahul\Documents\visual studio 2013\Projects\UDF_Trial\UDF_Trial\bin\Debug\UDF_Trial.dll'
WITH PERMISSION_SET = UNSAFE;
GO

and the Problem is solved.

answered Mar 20, 2014 at 11:44

Rahul

2,3076 gold badges35 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting error when using UDF with SQL Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related