SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can use clr function in select but not in update


Can use clr function in select but not in update

Author
Message
Japie Botma
Japie Botma
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 340
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
South Africa
Lowell
Lowell
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68596 Visits: 40900
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
Japie Botma
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 340
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
South Africa
Japie Botma
Japie Botma
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 340
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
South Africa
Japie Botma
Japie Botma
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 340
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
South Africa
Lowell
Lowell
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68596 Visits: 40900
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
Japie Botma
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 340
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
South Africa
Japie Botma
Japie Botma
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 340
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
South Africa
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search