CLR and UDF

Question

Post reply

CLR and UDF

Muhammad Furqan

Default port

Points: 1461
More actions
January 24, 2008 at 4:25 am

#182287

I have a table "m_table", containing a column "theCount", that is a derived column and contains a UDF (say "myUDF") in its definition.
When i do "select thecount from m_table" in SQL Server query browser, result is OK. But, when the same column is selected by a UDF designed in .net CLR, it gives following error.
This statement has attempted to access data whose access is restricted by the assembly
the .net code contains following way of connecting to the database
using (SqlConnection conn = new SqlConnection("context connection=true"))
I think, that "myUDF" doesnt have sufficient privileges for .net CLR.
Any idea ????

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

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

kimjongyouly SSC Enthusiast Points: 144 More actions · Answer 1

IF your code are correct, you may fix up the problem with 'aseembly permmistion_set'

CREATE ASSEMBLY assembly_name

from

WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE }

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

well!!!

i am only able to run following

ALTER ASSEMBLY sqlserver_pat with PERMISSION_SET=SAFE

while

ALTER ASSEMBLY sqlserver_pat with PERMISSION_SET=UNSAFE

and

ALTER ASSEMBLY sqlserver_pat with PERMISSION_SET=EXTERNAL_ACCESS

gives me error.

ANDDDDDDDDDD, Safe permission set doesnt solve the problem either, can you please elaborate a little more.

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

hello guys, i was busy on another project, and now

I AM BACK TO TH SAME PROBLEM

Please give me an idea on this.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

You wrote:

...Safe permission set doesnt solve the problem either, can you please elaborate a little more.

Based on that, I have the feeling the code fails somewhere other than the SELECT statement. What other things does your code do, apart from the SELECT?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

Is the following specified at the top of your code?

[SqlFunction(Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read, SystemDataAccess=SystemDataAccessKind.Read)]

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

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

Heh... as you said... "Well!!!!"

What might be even more interesting is to find out what the CLR does "AAAAAAND" why you think you need a CLR to do it. 😉

--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)

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

firstly, i have mentioned DataAccess.Read at the top.

then why do is a CLR needed. here is the answer

i have a table like this

table=mytable

table_name column_name value

tableA col1

tableB col2

now the "value" column is derived. i dont know how to write a udf to get a "variable" column from a "variable" table.

thats why is used CLR.

CLR was working fine for normal columns of tables, but one column in a table, lets say "col2" in "tableB" is itself a dervied column and is having a UDF in its definition. i get error for that one when i do like

select * from mytable where table_name='tableB' and column_name='col2'

you got any idea ?????

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 8

Yeah... it's a simple "Name/Value" table that can easily resolved using various high performance methods (none of which require a CLR) including simple cross-tabs. What kind of an error message are you getting when you do like you said with ...

[font="Courier New"]select * from mytable where table_name='tableB' and column_name='col2'[/font]

--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)

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

oh man ! you are missing a point.

i know about that simple query that you hace written, but problem with me is that the "table_name" and "column_name" are variable. they are not always "tableB" and "col2" or "col1".

i need something where i can pass "table_name" and "column_name" columns as argument and it can retrieve that particular column from that particular table, whichever it is.

Regards!

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 10

Muhammad Furqan (2/21/2008)
oh man ! you are missing a point.
i know about that simple query that you hace written, but problem with me is that the "table_name" and "column_name" are variable. they are not always "tableB" and "col2" or "col1".
i need something where i can pass "table_name" and "column_name" columns as argument and it can retrieve that particular column from that particular table, whichever it is.
Regards!

Use dynamic SQL like this:

DECLARE @sql AS VARCHAR(200);

DECLARE @col AS VARCHAR(200);

DECLARE @table AS VARCHAR(200);

SET @sql = 'SELECT ' + @col + ' FROM ' + @table;

EXEC @sql;

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 11

Muhammad Furqan (2/21/2008)
firstly, i have mentioned DataAccess.Read at the top.
then why do is a CLR needed. here is the answer
i have a table like this
table=mytable
table_name column_name value
tableA col1
tableB col2
now the "value" column is derived. i dont know how to write a udf to get a "variable" column from a "variable" table.
thats why is used CLR.
CLR was working fine for normal columns of tables, but one column in a table, lets say "col2" in "tableB" is itself a dervied column and is having a UDF in its definition. i get error for that one when i do like
select * from mytable where table_name='tableB' and column_name='col2'

More precisely, your @sql string would look like this:

SET @sql = 'select * from mytable where table_name=''' + @table

+ ''' and column_name=''' + @col + '''';

EXEC @sql;

Does that answer your question?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

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

ok guys, i figured it out.

problem resolved.

what i did was that

1.i changed database owner from "administrator" to a database user like "sa"

2.set the trustworthy open to true

3.Recompiled the assembly with "external access" option.

and everything was fine this time.

That query doesnt give me an error when executed manually. but gives me error when the same executed through CLR. and the error was for those columns only where the column was a derived one, and contained a UDF as its definition.

Anyways, its DONE.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 13

Muhammad Furqan (2/21/2008)
oh man ! you are missing a point.
i know about that simple query that you hace written, but problem with me is that the "table_name" and "column_name" are variable. they are not always "tableB" and "col2" or "col1".
i need something where i can pass "table_name" and "column_name" columns as argument and it can retrieve that particular column from that particular table, whichever it is.
Regards!

Not if the "mytable" looks like you posted it as.

However, If it doesn't look like that, then the dynamic SQL suggestions that folks have written on this thread will do fine.

--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)

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 14

Muhammad Furqan (2/22/2008)
ok guys, i figured it out.
problem resolved.
what i did was that
1.i changed database owner from "administrator" to a database user like "sa"
2.set the trustworthy open to true
3.Recompiled the assembly with "external access" option.
and everything was fine this time.
That query doesnt give me an error when executed manually. but gives me error when the same executed through CLR. and the error was for those columns only where the column was a derived one, and contained a UDF as its definition.
Anyways, its DONE.

You probably want to move on, since this is resolved, but, for the record, using the CLR to accomplish this sort of thing truly is overkill.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]