Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can use clr function in select but not in update Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 3:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 23, 2014 11:33 PM
Points: 134, Visits: 187
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
Namakwa Sands
South Africa
Post #1468477
Posted Friday, June 28, 2013 6:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1468524
Posted Friday, June 28, 2013 7:36 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 23, 2014 11:33 PM
Points: 134, Visits: 187
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
Namakwa Sands
South Africa
Post #1468544
Posted Friday, June 28, 2013 8:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 23, 2014 11:33 PM
Points: 134, Visits: 187
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
Namakwa Sands
South Africa
Post #1468581
Posted Sunday, June 30, 2013 6:13 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 23, 2014 11:33 PM
Points: 134, Visits: 187
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
Namakwa Sands
South Africa
Post #1468861
Posted Sunday, June 30, 2013 9:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1468864
Posted Sunday, June 30, 2013 11:55 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 23, 2014 11:33 PM
Points: 134, Visits: 187
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
Namakwa Sands
South Africa
Post #1468930
Posted Monday, July 1, 2013 2:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 23, 2014 11:33 PM
Points: 134, Visits: 187
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
Namakwa Sands
South Africa
Post #1468968
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse