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.

One place you're going wrong (probably not directly related to the current error) is building queries using a string builder. You ought to be using parameters — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 19, 2012 at 7:37
Console.WriteLine("Error.."); is likely causing it. There is no Console in SQL. — leppie
– leppie, Commented Oct 19, 2012 at 7:38
@leppie: Thank you, you are right. No exception now :) but not able to insert into table. — Shami C
– Shami C, Commented Oct 19, 2012 at 7:44
@ShamiC: There should be a way to signal the 'error text' to SQL. — leppie
– leppie, Commented Oct 19, 2012 at 7:57
You're not going to see any errors whilst you have catch (Exception) {;} in there. Take that out (for now/forever) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 19, 2012 at 8:06

DarthVegan · Accepted Answer · 2015-02-02 00:38:25Z

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

answered Feb 2, 2015 at 0:38

DarthVegan

1,26910 gold badges29 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

Error in Calling SQL CLR function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related