UDF execution through a CLR

Question

Post reply

UDF execution through a CLR

Muhammad Furqan

Default port

Points: 1461
More actions
July 20, 2008 at 10:51 pm

#210184

Hello Guys!

I have a view that contains some columns executing some UDFs like

create view vvv

as

select col1,dbo.myudf(col1) as col2 from myTable

now, when col1 is selected from view through CLR, its fine, but when the same done for col2, it throughs .Net exception that i dont have permission to execute.

Please note that "myudf" accesses other some other table too, from which columns can be accessed through CLR.

Please help guys,

its urgent!

Thanks all in advance.

Viewing 15 posts - 1 through 15 (of 19 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

You are going to have to provide us with more information. Please post a sample script and CLR code that can demonstrate the problem.

See this link for some guidelines on how to do this: http://qa.sqlservercentral.com/articles/Best+Practices/61537/

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Muhammad Furqan Default port Points: 1461 More actions · Answer 2

Muhammad Furqan

Default port

Points: 1461

July 21, 2008 at 6:04 am

#845702

Sorry guys!

that was my fault.

i didnt troubleshoot it well and finally figured out that it was not the UDF causing problem, it was something else.

Thanks again!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Muhammad Furqan (7/21/2008)

Sorry guys!

that was my fault.

i didnt troubleshoot it well and finally figured out that it was not the UDF causing problem, it was something else.

Thanks again!

Could you tell us what? Might be useful to know if someone else has the same problem.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Muhammad Furqan (7/21/2008)

Sorry guys!

that was my fault.

i didnt troubleshoot it well and finally figured out that it was not the UDF causing problem, it was something else.

Thanks again!

What was the something else? Might happen to me someday and that knowledge would be useful. Thanks... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Muhammad Furqan Default port Points: 1461 More actions · Answer 5

Muhammad Furqan

Default port

Points: 1461

July 22, 2008 at 2:24 am

#846212

it was just that i was not handling exceptions properly in CLR, like i was doing like

command.ExecuteScalar().toString()

but i was not handling what to do if "command.ExecuteScalar()" doesnt return any value.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

Muhammad Furqan (7/22/2008)

it was just that i was not handling exceptions properly in CLR, like i was doing like

command.ExecuteScalar().toString()

but i was not handling what to do if "command.ExecuteScalar()" doesnt return any value.

I am curious what you are doing in a CLR UDF that you can't do inline in TSQL here? There isn't enough information here to be certain, but I would venture to say that Jeff or one of the others on here could easily tell you how to do this inline in your view, and you will have much better performance from it rather than transitioning data between CLR and TSQL like you currently are, which is going to be RBAR (See Jeff's signature for explanation of this term, he coined it).

Muhammad Furqan Default port Points: 1461 More actions · Answer 8

Muhammad Furqan

Default port

Points: 1461

July 23, 2008 at 3:29 am

#846880

ok, i have a table like this

create table (

id int,

colname varchar(255),

date smalldatetime);

i need to have some view where i could select id,colname,date and

Now tell me how can that be accompalished without CLR?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

OK... maybe I'm missing something, but why would you want to do that with a CLR? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Jeff Moden (7/23/2008)

OK... maybe I'm missing something, but why would you want to do that with a CLR? :blink:

Nope, I am wondering the exact same thing.

It was the ExecuteScalar() that made me ask my initial question. That told me that the function wasn't going to be optimal from a performance stand point since data is transitioning into the CLR layer with the parameter, then inside CLR it is requesting more data to be pumped back to the TSQL layer.

Unless there is some amazingly complex operation happening that we haven't seen the code for, then this is misplaced.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Jonathan Kehayias (7/23/2008)

Unless there is some amazingly complex operation happening that we haven't seen the code for, then this is misplaced.

Heh... I think about 99% of all CLR code is misplaced. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Muhammad Furqan Default port Points: 1461 More actions · Answer 12

hmmmm, ok ok guys!

please tell me how to get value of a "DYNAMIC" column from a table.

I am inputting column name whose value is to be selected into CLR. i tried a while ago to select a column inputted to a UDF but was giving me error.

Please tell me if that's possible.

I tried it again.

------------------------------------------

my UDF

------------------------------------------

create FUNCTION [dbo].[myUDF_temp]

(@col varchar(255))

RETURNS varchar(255)

AS

BEGIN

DECLARE @ResultVar varchar(255)

select @ResultVar=@col from temp_map where id='1215'

RETURN @ResultVar

END

------------------------------------------

this is how i called the function

------------------------------------------

select dbo.myUDF_temp('myVal') from temp_map;

------------------------------------------

where myVal is a column in temp_map. Now what i got as output was just a list of 'myVal'.

Tell me where i am wrong.

Thanks in advance!

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 13

Consider the following example:

CREATE PROCEDURE uspGetObjectIDFromName

(

@ObjectName sysname,

@OutputValue varchar(1000) output

)

AS

DECLARE @SQL nvarchar(max)

SET @SQL = 'SELECT @Output=convert(varchar(1000), '+@objectname+')

FROM sys.objects

WHERE name = ''sysfiles1'''

DECLARE @params nvarchar(max)

SET @params = N'@Output varchar(1000) OUTPUT'

EXEC sp_executesql @SQL, @params,

@Output = @OutputValue Output

GO

DECLARE @Output varchar(1000)

EXEC uspGetObjectIDFromName 'object_id', @output Output

SELECT @Output

EXEC uspGetObjectIDFromName 'name', @output Output

SELECT @Output

EXEC uspGetObjectIDFromName 'type_desc', @output Output

SELECT @Output

This is not a UDF, but it does exactly what your example does, without the cost of the switch to CLR, and for performance, it will beat your CLR implementation. The added bonus is that you can specify EXECUTE AS for the procedure, and preserve security.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

If you want the object ID from the name, why not just use...

SELECT OBJECT_ID('objectnamehere')

There are several system functions that will help keep you from redeveloping the wheel... just look them up in BOL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions