Error in Calling SQL CLR function

Question

I am writing a CLR function to parse a table column and write the results into another table. Basic requirement is parsing the Detail column, which contains a Time part and an ID part. Results will be the time difference between two Ids.

Ex: Time1,Id1;Time2,Id2;Time3,Id3... and so on Time2-Time1 is time taken by Id1 in seconds.

Same function is working in normal console application but CLR function is throwing the exception when I call it from SQL server. Error is

A .NET Framework error occurred during execution of user-defined routine or aggregate "Function1": 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: UI System.Security.HostProtectionException: at UserDefinedFunctions.Function1(String msisdn, String promptdetails)

My code is: SqlConnection conn = new SqlConnection(); SqlCommand cmd = new SqlCommand();

    int count = 0;
    string PromptPart = string.Empty;
    string PrevPromptPart = string.Empty;
    DateTime TimePart;
    TimePart = new DateTime();
    DateTime PrevTimePart;
    PrevTimePart = new DateTime();
    TimeSpan difference;

    try
    {
        count++;
        conn.ConnectionString = "Context Connection=true";
        cmd.Connection = conn;

        String[] string1 = promptdetails.Split(";".ToCharArray());
        foreach (var item1 in string1)
        {
            count++;
            String[] string2 = item1.Split(",".ToCharArray());
            PromptPart = string2[1];
            TimePart = DateTime.ParseExact(string2[0], "M/d/yyyy h:mm:ss tt", System.Globalization.CultureInfo.InvariantCulture);
            if (count > 1)
            {
                StringBuilder sbQuery = new StringBuilder(1024);
                sbQuery.Append("INSERT INTO [Shami].[DBO].[data] (MSISDN,PromptID1,PromptID2,TimeDifference) VALUES");
                sbQuery.Append("('");
                sbQuery.Append(msisdn);
                sbQuery.Append("',");
                difference = TimePart.Subtract(PrevTimePart);
                sbQuery.Append("'");
                sbQuery.Append(PrevPromptPart);
                sbQuery.Append("','");
                sbQuery.Append(PromptPart);
                sbQuery.Append("',");
                sbQuery.Append(difference.Seconds);
                sbQuery.Append(")");
                string sub = string.Empty;
                sub = sbQuery.ToString();
                try
                {
                    conn.Open();
                    cmd = new SqlCommand(sub);
                    SqlContext.Pipe.ExecuteAndSend(cmd);
                }
                catch (Exception ie)
                {
                    Console.WriteLine("Error..");
                }
            }
            if (count <= 1)
            {
                StringBuilder sbQuery = new StringBuilder(1024);
                sbQuery.Append("INSERT INTO [Shami].[DBO].[data] (MSISDN,PromptID1,PromptID2,TimeDifference) VALUES");
                sbQuery.Append("('");
                sbQuery.Append(msisdn);
                sbQuery.Append("',");
                sbQuery.Append("'_'");
                sbQuery.Append(",");
                sbQuery.Append(PromptPart);
                sbQuery.Append(",");
                sbQuery.Append("'0'");
                sbQuery.Append(")");
                string sub = string.Empty;
                sub = sbQuery.ToString();
                try
                {
                    conn.Open();
                    cmd = new SqlCommand(sub);
                    SqlContext.Pipe.ExecuteAndSend(cmd);
                }
                catch (Exception ie)
                {
                    Console.WriteLine("Error..");
                }
            }
            PrevPromptPart = PromptPart;
            PrevTimePart = TimePart;
        }



    }
    catch (Exception)
    { ;}
    finally
    {
        conn.Close();
        conn.Dispose();
        cmd.Dispose();
    }
    return msisdn;

Please let me know where I'm going wrong. I can't debug in CLR.

Answer 1

You would just need to replace your line

Console.WriteLine("Error...");

with this line:

SqlContext.Pipe.Send("Error...");

This will do the same thing for you and it will run