Can use clr function in select but not in update

Question

Can use clr function in select but not in update

Japie Botma

SSCrazy

Points: 2990
More actions
June 28, 2013 at 3:33 am

#274613

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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Japie Botma SSCrazy Points: 2990 More actions · Answer 2

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

Japie Botma SSCrazy Points: 2990 More actions · Answer 3

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

Japie Botma SSCrazy Points: 2990 More actions · Answer 4

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

(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

Lowell SSC Guru Points: 323518 More actions · Answer 5

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!

Japie Botma SSCrazy Points: 2990 More actions · Answer 6

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

Japie Botma SSCrazy Points: 2990 More actions · Answer 7

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