Can use clr function in select but not in update

  • I have a complicated clr function that does a formula calculation. The variable values of these formulas comes from different databases and that is the reason for the clr function.

    Now, with the following T_Sql:

    SELECT fncFormula(1, 2013, 6, 28) as Value

    I get a result within 1 second.

    When I want to update a table with that result or just dump the result into a temp table, it takes about 2 minutes and then returns a value of 0.

    Now it seems that sql do not trust the result of the clr function, but what is the use if I can't use the value?

    The clr permission level is set to External and the database property Trustworty is on.

    Is there something else I am missing here?

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • Japie i personally use CLR functions all the time, so it's probably how you are using it in an update, rather than the CLR being a problem all by itself.

    can you show us your sample UPDATE statement you might use?

    if the CLR is being used against a lot of rows, you might want to change it from a scalar function to a table value function instead; it returns results faster, but the usage is more akin to joining it to a table.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • It does not even allow an insert into a temp table. The clr returns a single value, thus:

    CREATE TABLE #Temp (

    tValue NUMERIC(18,4))

    INSERT INTO #Temp

    SELECT fncFormula(5, 2014, 6, 28)

    or

    SELECT fncFormula(5, 2014, 6, 28)

    INTO #Temp

    Both fail because it inserts a zero value, but "SELECT fncFormula(5, 2014, 6, 28)" returns the correct value.

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • I have removed all my try catches in the clr and I just found that I have casting issues. I will reply when that is solved.

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • With that out of the way, my original problem did not disappear.

    TEST 1:

    SELECT CONVERT(INT, nsFramework.dbo.fncFormulaCalculation(5, 1, tYear, tMonth, 0))

    FROM dbo.tblTallyTable_Periods

    WHERE tYear = 2014

    -----------

    26746

    23014

    27048

    26326

    27356

    25219

    27667

    27823

    25650

    28139

    27386

    26956

    TEST 2: (fail)

    CREATE TABLE #Temp (

    Result INT)

    INSERT INTO #Temp

    SELECT CONVERT(INT, nsFramework.dbo.fncFormulaCalculation(5, 1, tYear, tMonth, 0))

    FROM dbo.tblTallyTable_Periods

    WHERE tYear = 2014

    SELECT * FROM #Temp

    DROP TABLE #Temp

    (12 row(s) affected)

    Result

    -----------

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    (12 row(s) affected)

    Now why would the exact same statement fails only because I am trying to insert the result into a temp table? I know it is the clr function that is returning the zeroes, because I set the result to 0 when an error is occured. So something changes when I try to write back to SQL and the clr function knows about the change. The question is what must I change in the clr for it to work the same with test 1 and test 2?

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • instead of returning a zero when there's an error, just throw a sqlexception, and that will return the full error message, so you can track it down completely.

    if it's a logic error(if.. else return 0), create a string with some diagnostic info, and raise a sql exception with that information.

    i could help with some peer review if you want to paste the body of your CLR function .

    Try {

    // code here

    }

    catch (SqlException odbcEx) {

    // Handle more specific SqlException exception here.

    }

    catch (Exception ex) {

    // Handle generic ones here.

    }

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Test 1 proves that my code works when you just view it. It is the INSERT INTO that is causing something to change on the clr side, as if you need to tell Sql to trust the clr. There must be a property to set or you cannot use a complex clr function in Sql update statements.

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • I have determined that the "out of context" connections in the clr is causing the problem. Does that mean that I cannot connect to 2 different databases when I use the result of the clr function for updating, but it works in a select? Or is there another way to kill the "cat"?

    5ilverFox
    Consulting DBA / Developer
    South Africa

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply