Testing if CLR is enabled from within a function

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

  • 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#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • 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".

  • 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 :(. 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#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • 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
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • 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#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Tricking the T-SQL compiler is a clever solution. Better, performance penalty is insignificant. Thanks a lot!

  • Viewing 7 posts - 1 through 6 (of 6 total)

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