Post reply

CLR and UDF

  • 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 ????

  • January 24, 2008 at 8:42 pm

    #771574

    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 }

  • January 24, 2008 at 10:38 pm

    #771587

    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.

  • February 15, 2008 at 12:10 am

    #778734

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

    I AM BACK TO TH SAME PROBLEM

    Please give me an idea on this.

  • February 16, 2008 at 3:19 am

    #779087

    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]

  • February 16, 2008 at 3:37 am

    #779089

    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]

  • February 16, 2008 at 10:55 am

    #779113

    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)
    Intro to Tally Tables and Functions

  • February 21, 2008 at 3:26 am

    #780561

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

  • February 21, 2008 at 4:20 am

    #780572

    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)
    Intro to Tally Tables and Functions

  • February 21, 2008 at 11:07 pm

    #780963

    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!

  • February 21, 2008 at 11:24 pm

    #780965

    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]

  • February 21, 2008 at 11:34 pm

    #780966

    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]

  • February 22, 2008 at 3:14 am

    #781012

    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.

  • February 22, 2008 at 5:40 am

    #781051

    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)
    Intro to Tally Tables and Functions

  • February 22, 2008 at 7:38 am

    #781101

    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]

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

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