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


Testing if CLR is enabled from within a function


Testing if CLR is enabled from within a function

Author
Message
cmartel 20772
cmartel 20772
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 174
I just wrote my first CLR function. It is a C# rewrite of a T-SQL function that crawls the nodes of large diagram in fraction of a second instead of nearly one minute.

Since I want my application to be independent from the decisions of DBAs, I am testing the 'clr enabled' value in order to call either the CLR or the T-SQL function from within a wrapper T-SQL function with code similar to:

IF EXISTS(SELECT NULL FROM sys.configurations WHERE name = 'clr enabled' AND value = 1)
INSERT INTO @tempTable SELECT * FROM clrFastFunction
ELSE
INSERT INTO @tempTable SELECT * FROM tsqlSlowFunction

Problem is that SQL server gives the error "Execution of user code in the .NET Framework is disabled" as soon as clr is disabled, like if clrFastFunction was called anyway.

Is there a way to achieve what I want to do?
Solomon Rutzky
Solomon Rutzky
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3294 Visits: 3029
Just encapsulate the SELECT for the INSERT in an EXEC so that it is not a parse-time issue. For example:

DECLARE @Table TABLE (IntVal INT NOT NULL)

IF (1 = 0)
BEGIN

INSERT INTO @Table (IntVal)
EXEC('
SELECT IntVal
FROM SQL#.Util_GenerateInts(1, 4, 1)
')
END



What is inside of the EXEC is not parsed unless the EXEC itself is run. I get around many parse-time issues in this manner :-).

Take care,
Solomon..

SQL# - http://www.SQLsharp.com/
cmartel 20772
cmartel 20772
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 174
Using EXEC works well in a standard script but calling it from within a function gives the error "Invalid use of a side-effecting operator 'INSERT EXEC' within a function".
Solomon Rutzky
Solomon Rutzky
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3294 Visits: 3029
cmartel 20772 (3/5/2014)
Using EXEC works well in a standard script but calling it from within a function gives the error "Invalid use of a side-effecting operator 'INSERT EXEC' within a function".


Yeah, I missed that part the first time I read it Sad. Does it need to be in a function or can it be in a Stored Proc? I just tried encapsulating the CLR function call in an Inline TVF but got the parse-time error.

SQL# - http://www.SQLsharp.com/
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10381 Visits: 7891
It's fugly, but have you tried wrapping your clr function in a t-sql function?

That will prevent the parser error.

I have tested it on a "hello world" function to make sure it works.

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Solomon Rutzky
    Solomon Rutzky
    Hall of Fame
    Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

    Group: General Forum Members
    Points: 3294 Visits: 3029
    Yes, a Multistatement TVF (as opposed to an Inline one like I had tried earlier, or even a View) seems to work.

    So first this:
    CREATE FUNCTION dbo.TestWrapper()
    RETURNS @Temp TABLE (IntVal INT)
    BEGIN

    INSERT INTO @Temp (IntVal)
    SELECT IntVal
    FROM SQL#.Util_GenerateInts(1, 4, 1)

    RETURN

    END



    and then this should work:
    DECLARE @Table TABLE (IntVal INT NOT NULL)

    IF (1 = 0)
    BEGIN

    INSERT INTO @Table (IntVal)
    SELECT IntVal
    FROM dbo.TestWrapper()

    END



    However, if this is called repeatedly or if the CLR function returns a lot of rows, doing a Multistatement TVF could be inefficient. In that case you would be left with the Stored Procedure option.

    Hope that helps.

    Take care,
    Solomon...

    SQL# - http://www.SQLsharp.com/
    cmartel 20772
    cmartel 20772
    SSC Veteran
    SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

    Group: General Forum Members
    Points: 224 Visits: 174
    Tricking the T-SQL compiler is a clever solution. Better, performance penalty is insignificant. Thanks a lot!
    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